find script (fill in)

[sirtemplar]

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 October 27, 2007 by Guest

[Fenton]

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.)

[Tusif Ahmad]

Hi,

I tried to do your job by scipting.

I am sending you the file have a look, may be it will solve your issue.

Regards,

Tusif

x.zip

[sirtemplar]

thanks for the reply. both did not work however. the script i thought did it. when i run it it placed 3200 but when i changed the value on f2 it still gives 3200, and even whatever i put there.

[comment]

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.

LookUpHigher.fp7.zip

[sirtemplar]

i followed your example and yet see that on mine it doesn't work well :(

trial.zip

[comment]

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.

[sirtemplar]

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.

Contrib.zip

[comment]

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.

LookUpHigher2.fp7.zip

[sirtemplar]

thanks anyway. i will look at your file and study it. i will post if something works out. thanks again.

p.s. yes, you're correct about the summary running total.