Jump to content

find script (fill in)


This topic is 6077 days old. Please don't post here. Open a new topic instead.

Recommended Posts

This is my scenario: please, please help!

i have database with two tables looking something like this.

table 1

record - field1 - field 2

1 - 22 - 5,600

2 - 30 - 7,600

3 = 56 - 3,200

4 = 60 - 1,800

5 = 90 - 10,000

6 = 100 - 35,000

table 2

field = value

f1 = some text

f2 = 54

f3 =

you noticed that f3 in table 2 is blank. what i want is to fill this field via calculation doing this:

first sort table 2 (ascending value of fiels1) then;

look for the record in table 1 which is equal or the first greater than to f2 of table 2 (54). in the case above it is 56 (record 3, table 1) and put the value of field 2 of that specific record (which in tha case above is 3,200)

after running of the script therefore f3 and table 2 should have 3,200 as value.

thank you. this is the only thing i do manually on my database!

Edited by Guest
Link to comment
Share on other sites

What you are describing is a classic case of Lookup, with the option "Next higher." It has been in FileMaker from the beginning I believe.

1. Define a relationship: table2::f2 = table1::field1

2. Define the field table2::f3 with the auto-enter option, Lookup, based on the above relationship. In the lower section of the dialog, on the left side, lookup from field relationship::field2. On the right side, if no match, lookup next higher.

3. In table2, show all relevant records. Put your cursor in the table2::f2 field. Go to the Records menu, choose the command Relookup Field Contents. Table2::f3 will fill in, in all found records.

4. From now on whenever you enter or modify the value in field table2::f2, field table2::f3 will relookup its value.

It's in the FileMaker Help, but there's not much about the Next higher or lower. It's fairly obvious though, once you see it happen. It works with either number or text fields, but is trickier with text, as it sorts alphabetically, requiring "padding" if using numbers in a Text field. I'm assuming yours above are Number fields.

(P.S. I hope I got all the table#;):field#'s right.)

Link to comment
Share on other sites

Fenton's suggestion will work if you implement it correctly. It's hard to guide you when all you say is it "did not work". See the attached file.

Depending on what you use this for, you could also view the relevant related value directly on the layout, without putting it in another field. Or make the field an unstored calculation, instead of a lookup. For this you would make the relationship based on the < operator, and sort the related records by value, ascending.

The difference between the two methods comes to play when you change some values in the data table. With a lookup, the looked up value is 'frozen' until you perform a relookup. The other method always relies on the current related value.


Link to comment
Share on other sites

It seems that your relationship is using the wrong field - it's hard to tell when you're using such cryptic names for your fields and tables.

If by chance this has to with looking up the price according to quantity, I suggest you use the LOW limit of each bracket (i.e. the lowest price starts at a quantity of 1 or 0), and lookup the next LOWER price. This way it will work when the quantity exceeds the largest limit.

In any case, using the above example, the relationship matches quantity to quantity, and the lookup copies the price.

Link to comment
Share on other sites

i am herewith attaching my database. it is working as i want it except for one step i do manually, (i am asking how i can automate it here if possible!).

what i do manually is this...

1) on table BUDGET i have data MEDIAN #PERSON which is a result of a calculation.

2) i look at the value of MEDIAN #PERSON

3) i go to table CONTRIB and I order the field FORMEUR ascending

4) then i look at the GETMEDIAN field and look for the value which is equal or first greater than value to that of MEDIAN #PERSON (in table budget). in the case i hav here attached it is 681 for 2007 and 648.5 for 2008 (this will of course vary every year.

5) then i look at the field COSTPERPERSON and look for the value of that EQUAL or first greater than in no.4

-- in the database attached the first greater than ot 681 (2007) is 4732.4593220338983051

-- and for (2008) it is 6441.1764705882352941

6) now the last step is to copy and paste this value on table BUDGET on field MEDIAN COST

7) that's all. it is pretty tiring so i was wondering if the above process can be done automatically in any way (eg. a script or lookup as some suggests!)

8) it is important to note that the tables are related according to YEAR AND GROUP!

9) if the above cannot be done automatically, then i will just have to do it manually as above.

thanks for the willingness to help.


Link to comment
Share on other sites

I'm afraid your file is too confusing for me. The only thing I understood from your explanation (I think) is that the field that determines which record to look for is an unstored calculation summing related records. If that's true, a lookup will not work here - you will need to use the second method mentioned in my first post.

I suggest you study the attached file, which is an attempt to show your problem in isolation. Note that a refresh is required if the child values are modified, because the results of one relationship depend on another.


Link to comment
Share on other sites

This topic is 6077 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.