Relooking up Field when value changes?

[spankalee]

Is there any way to force a relookup when the value of the looked up field changes? I can't see any way to trigger a relookup from a validation script...

If that's not possible maybe I get some advice on how to handle this situation.

I have an Invoicing/Inventory system. There's 3 tables in question: Invoices, InvoiceLineItems, and Inventory. InvoiceLineItems are related to the Inventory based on product code and status (order, back order, hold). The status is part of the Invoce, but since I'm relating InvoiceLineItems I need to make it a regualr stored field in LineItems and lookup the value based on InvoiceID.

When the status of the Invoice changes I was to automatically relookup the status fields of the LineItem.

This situation actually pops up all over my inventory system, but so far the value in question has been a boolean so I solved the problem by creating a button and script that toggled the value and performed the relookup. But now I have a drop down list and the only thing I can think of doing is putting a button next to the status field that says "save" or "refresh" and forcing the user to press it when they change the status. I can see that being a disaster.

So that's why I want to trigger the relookup when the value changes. Any suggestions on how to handle this situation?

Thanks,

J

[Ender]

I like the idea of using a button to refresh something as potentially time consuming as a relookup. But if you're using FM7, there is a way to launch a script after exiting a field. You can use the example plug-in that comes with FM7 Developer. There is a good example of this on the Database Pros website . (Click the New button on the bottom and look for the "Hidden Plug-ins" example.

Are you sure you need the Status field stored in the InvoiceLineItems? FM7's new relational structure has made many of our old techniques obsolete. Maybe you can say more about why storing these values is necessary (especially if they're supposed to change when the source changes.) There may be a cleaner way to do what you need.

[spankalee]

Ender said:

I like the idea of using a button to refresh something as potentially time consuming as a relookup.

Why should the lookup be time consuming? Isn't it only going to do the relookup on the related records? That would only be the line items for that one invoice.

But if you're using FM7, there is a way to launch a script after exiting a field. You can use the example plug-in that comes with FM7 Developer. There is a good example of this on the Database Pros website . (Click the New button on the bottom and look for the "Hidden Plug-ins" example.

I'll look into that, but I don't have Developer.

Are you sure you need the Status field stored in the InvoiceLineItems? FM7's new relational structure has made many of our old techniques obsolete. Maybe you can say more about why storing these values is necessary (especially if they're supposed to change when the source changes.) There may be a cleaner way to do what you need.

I'm pretty sure that I need Status stored in InvoiceLineItems because I'm basing a relationship off it. I tried making it a calculation field, and FM7 didn't yell at me like previous versions would, but the relationship never worked.

My relationship is something like this:

Inventory::ProductCode = InvoiceLineItems::ProductCode

AND InvloceLineItems::Status = HoldStatusGlobal

I would much rather not store Status in LineItems if I can avoid it, but I don't see how that's possible.

J

[spankalee]

You know one thing I haven't looked into (I'm kindof a FM newbie even though I've been working on this for a long time) is not saving record changes automatically and making the user press a "Save" button when they're done editing the invoice.

If I can pop up a dialog when they're exiting the record without saving, then that would be a good solution I think...

[RalphL]

You can force a re-lookup by replacing the key field with itself. This can be done with a loop script and set field or with the replace command.

[Ender]

I see your problem with trying to relate to Line Items for a specific Invoice Status. The lookup might make sense here.

There is a way to show Line Items for a specific Status while keeping the Status in Invoice, but it seems to have a refresh problem: by putting a global StatusChoice in the Line Item table, you can use that as part of the compound relationship to Invoice. I have attached an example showing how this would work with a portal in a layout based on the Product table, showing Invoice (with line item) records with the specified status for the current product. This works, but when you change the global Status Choice, it does not properly refresh unless you leave the record and come back. This could still work for yor solution if you scripted it right.

Oh, you don't need Developer to try the plug-in. And I said a relookup could be time consuming because I was considering WAN speeds, which can pretty slow with batch operations.

[spankalee]

Ender, thanks a lot for the help man...

I tried the plug-in and that works great. I call my "RefreshStatus" script from the calculation for the Invoice::Status field and my LineItems are updated and I get to keep my nice relationship.

I did notice though that relookup wants to process all LineItems, regardless of whether or not they're part of the current Invoice. This could be a problem because the database could easily hold 100,000 LineItems by the end of the year.

Right now my script calls relookup from the Invoices table (you know, I'm a little confused whether scripts care which table you're browsing when you call them in FM7) on InvoiceLineItems::InvoiceID. I was hoping the relationship between Invoices and LineItems would limit the relookup to the relevant handfull of LineItems, but no such luck. Do you think it would be better to switch to a LineItems layout, perform a find on the InvoiceID and then do a relookup? Or is there a better way I can limit the relookup? The DB is used from about 4 computers in one office now, but will probably be used over a WAN all the way to Asia in the future.

I'm real interested in the possibilities that plug-ins open up. One thing I want now is a simple plug-in that will either run a script when exiting a record, or replace the save record dialog that FM pops up. Can I develop plug-ins with out FM Developer?

Ok, I'm going to look at the files you attached now.

Thanks again,

J

[as a side note I have to say I'm amazed at the hoops FileMaker makes you jump through to do some fairly standard things. Drawing a GUI is nice, but this stuff is just a PITA. I'm missing Java and MySQL at the moment.]

[Ender]

Try a Go To Related Records [ Show, "Line Items" ] script step before going to the Line Items table and performing the Relookup. You should probably error trap this; making sure you have a reasonable found set before the relookup (you wouldn't want it to relookup on all records over a WAN.)

Maybe someone else knows about your plug-in questions. Not my thing.

[And I haven't done coding with Java or SQL for years. Yucky.]

[Fenton]

I would just add that, as your 1st post has v.7 on the sidebar, you would want that Go To Related Records [ Show, "Line Items" ] to also [x] Use External Table's Layout, with a table in the Line Items file. In 6 you would want to go to a layout in your external script (in another file, how quaint :-) Possibly all your Line Items layouts have the field anyway.

Relookup used to be one of those steps that required the field to be on the current layout (and obviously in the current table). I don't know if they've changed that "field on the layout" part for 7 (vaguely remember reading it, anyone?). I seldom use Relookup, partly 'cause of these limitations.

View as Form, then a Loop setting the ID that controls the lookup does more or less the same thing at about the same speed.

Remember to Commit Record at the end -- in the Parent file in v.7. That commits the related records also (otherwise they stay open!). Read about this in Ilyse Kazar's article about record locking, on FileMaker's Migrations page (it's in the big PDF).

[The Shadow]

I just tried it.

Relookup doesn't require the field to be on the layout anymore. The other one I'd seen mentioned is Replace Field Contents doesn't need the field on the layout either.

[spankalee]

Interesting... I was just messing with some settings now, and I realized that when I do a relookup from an Invoice layout on InvoiceLineItems::InvoiceID that it's performing the relookup on all Invoice records, not on all InvoiceLineItems. This makes me think that if I just reduce the found set to the current Invoice before the lookup then I won't be running relookup on the whole database.

Finding just the current record is easy, but is there any way to return to the previous found set after I'm done, so I don't mess up someone's find when they're only changing a field value?

Fenton, personally I like your loop Idea, but I'm slightly confused about how FM7 puts scripts into context. In a programming environment this would be easy: Do a query for related LineItems, loop through them and update the value. Well the portal is really the query, but there doesn't seem to be a way to loop through the records in a portal. It looks like I'd have to switch to a LineItems layout and loop there. That kinda sucks. Also, as I'm beginning to realize that a scripts behavior can change depending on the layout you're viewing, it's seems that most scripts should probably have a GotoLayout[] as the first command, huh.

[Fenton]

It would perhaps be easier if did think of FileMaker more like a programming language. Many people are confused by the dual nature of portals. The portal's relationship gives you direct access to the Line Items table. All you need is the single step,

Go To Related Record [show, "InvoiceID to LineItems InvoiceID relationship"]

(As you say, you don't need a "query," you're already connected to those records via the relationship.)

You are now in a found set in the line items table of only those records. Version 7 is sensitive about context, so if you're going to be looping through records you'd want to switch to a layout for the Line Items table. This can be done right in the step now, with the "Show record using layout" drop-down.

(Actually it was pretty much automatic in 6; GTRR switched you to the targetted file; but not to a specific layout.)

It doesn't really suck. You will not even see it happening if you include a step to go back to the original layout. FileMaker doesn't refresh intermediate layouts in scripts unless you do something that forces it. Freeze Window in the original layout at the first often makes it even smoother.

I know all this "layout" this and that seems like extra baggage. But in FileMaker 7 I believe it will enable never-before-seen tricks, once we really learn how to use the Table Occurrences. So far I'm mostly concentrating on just getting the right ones :-/

[spankalee]

Sweet Fenton! that solves everything very nicely.

My script now looks like this:

Go to Related Record [show only related records; From table: "InvoiceLineItems"; Using layout: "InvoiceLineItems" (InvoiceLineItems)]

Relookup Field Contents [No dialog; InvoiceLineItems::InvoiceID]

Go to Layout [original layout]

Ands that's it. Works like a charm, only performs the relookup on the related line items, and you're right the screen never switches to the line items layout.

Between the plug-in and this improvement everything works as smooth as silk now.

J

[-Queue-]

Does Relookup have multi-user issues in 7? It may be safer to run a Loop with Set Field [table::serial; table::serial] and test for Get( LastError ) = 301, to ensure all related records are actually updated.