Performing lookups when match field changes

[Michael Thomas]

In the help files for FMP (5.0) it says

[BobWeaver]

It definitely should relookup when any field referenced by your calculation field changes. What formula are you using for your calculation field?

[Michael Thomas]

Had a little trouble getting on to post this and now the phone line has gone a bit dodgey...

Let

[BobWeaver]

Okay, I see the problem. Length is in another file, and Filemaker won't notice a change to that value in the other file. This leaves me a bit confused. I had assumed that you have an invoice file and a price list file in which to lookup prices. If that's what you are doing, you should have a relationship set up like this:

Invoice::Type_L = PriceList::Type_L

where Type_L = Type & Right("000"&Length,3) in both files.

Do you have a third file? If so, I need more info about how you have things structured

BTW, there's nothing wrong with your leading zero method. That's fairly standard.

[Michael Thomas]

Yes I do have a third file. The three files are:

Invoices, (contains the Length field)

Line Items (contains the Type, Type_L calculation field and a Line Price field), and

Info (which has the price list from which I lookup the price into the Line Items file)

If I understand correctly, you

[BobWeaver]

quote:

---

If I understand correctly, you

[Michael Thomas]

quote:

---

Why don't you have both Length and Type in your line items file rather than the invoice file? If you do, you should be able to lookup the price in your line items file from the Info file.

---

Unfortunately, the same issue (involving the Length field) happens in more than one file. If I move the Length field to the Line Items file I'll still have the same problem in the other files. It seems I need to either restructure the DB to completely avoid this problem or find another way around it.

After looking everything over I'm thinking of using a script to make a change to another field (Update) in the same file as where the lookup is performed. Any time the Length field is changed Update (which will be involved in the concatenation field, Type_L, but will not actually change anything) will force the lookup to be re-performed. Something along the lines of:

Type_L = Type & Right("000"& Length,3) & If(Update * 0 = 0, "", "")

This seems to work fine in my limited testing so far. I'm using a button that excutes a script that goes to a different layout where the dimensions are entered, then the SetField step sets the Update field and returns to the original layout. Although it seems a bit convoluted in writing, it may actually help the user interface work a bit better as well. Any comments on this approach?

I really appreciate all your help (as well as the rest of this forum.) I am slowly learning my way but this has been extremely helpful.

Thanks,

Mike

[Michael Thomas]

Sorry , Double post.

[ May 28, 2001: Message edited by: Michael Lye ]