Hi Guys

I have a lookup script that wont break the loop and keeps on evaluating my data.

What it is supposed to do:

I have two related databases, the 'order details' database and the 'invoice' database.

When an order/record is created the invoice date (in the 'invoices' database) isn't created until I print the invoice (a script sets the invoice date field with the current date). Therefore this look up from the 'orders' database checks against the invoice number to see if an invoice date exists in the 'invoices' database. Can anyone tell me what is going wrong?

Show All Records

Go To Record/Request/Page [First]

Loop

Go To Field [Orders::Invoice Date]

Relookup Field Contents [No Dialogue; Orders::Invoice Number]

Go To Record/Request/Page [Next; Exit After Last]

End Loop

Cheers Guys, I'm at a loss on this one.

You don't need to loop through them; the relookup updates all records anyway. You should also remove the Show All Records (as the file gets bigger, doing a relookup on all records will become terrible slow,) instead go to the correct Invoice's line items using Go to Related Records [ Show ].

Are you sure you need the Invoice Date in the line items? If it's just for display purposes, you can refer to the related Invoice Date.

I need the invoice date in the line items as many of the items have expiration dates based on the invoice date.

I guess I could add a script to the 'Print Invoice' button that goes to the related record in the 'Orders' database that sets the invoice date to each line item??

Yes, I think you should go to the line items file and set the date. Either a Loop with Set Field, or a single Relookup would work; but not a Loop with Relookup. Relookup will relookup all fields defined as lookups on the ID used, so take that into consideration.

It will fail on records that are "busy" in a multi-user environment. So will Set Field, but you can trap for the error. Worst case scenario is someone hits a Print button for this operation in the Invoice file, while someone else has their cursor parked in a portal row (to the line items file) on the same Invoice record. Not likely but possible.

If you Loop, Go to Field, then trap for an error, at least you'll know it happened, and you can display a custom message. Relookup I don't think will tell you (don't really know). Always View as Form when looping. Much faster.

BTW, the above is less of a problem in v.7. The record won't be locked, unless they're actually editing the field. Of course, it's likely they will be. It's a tough call. In most situations you would want an error at this point, because it means the data is changing and your printed data is going to be obsolete.

To best avoid locking, you can enter the data into global fields above the portal, then set into the portal row with a button-script. Or just tell people not to work on the same invoice at the same time, or use Set Field and ignore the problem unless it surfaces (which may be never).

Fenton said:

Worst case scenario is someone hits a Print button for this operation in the Invoice file, while someone else has their cursor parked in a portal row (to the line items file) on the same Invoice record.

That case isn't a problem in FM7, the Print runs with no hitch - the last committed value for the cursor parking record is printed, the Print will not give an error.