How to force lookup fields not to relookup?

[raymanj]

Hi,

I am trying to force my lookup fields not to relookup for certain records.

My setup:

I have a Customers.fp5 file and a Invoice.fp5 file. The Invoice file has a CustomeID field to relate all invoices with a customer. So when an invoice is created and the CustomerID field is filled, certain lookup fields are populated with the data from the customer record. Such as: address, phone numbers, discounts, etc.

Now the real problem. Sometimes some employees make mistakes and create more than one customer file for the same customer. These duplicate customer files are not realized until many invoices have been created for both customer records. So I am trying to write a script that will move all the invoices from one customer record to another.

Sounds simple. I thought all I hand to do was change the relating field (CustomerID) in each invoice record. Well, when I did, all the lookup fields linked to the CustomerID field, relookedup data from the new customer record. This would change all those invoices' data (address, discounts, etc). This was not what I expected.

So, how do I change this field, CustomerID, without relookingup linked fields.

Any help is welcomed.

[Ugo DI LUCA]

Hi,

I assume you defined a descending sort for your relationship, as usually FM would grab the first created "related" record.

If this is just a temporary Housekeeping script, then simply reverse your relationship sort for this purpose, and then the lookup will grab the first related record.

If similar procedures might be called later, involving a looping script, or a replace script, but you want to lock lookup for the old ones, then I'd suggest you switch your lookup-key to a new calculated field.

Your relationship is currently established as Foreign_ID::Record_ID

Create, for that instance 2 fields :

- a global field, g_control (same format as your Foreign_ID)

- c_TriggerLookupKey = Case(Record_Id = g_controlKey, Record_ID & "0 ", rECORD_id)

Note the space in between the quotes.

Example for a Loop script step.

After the Find has been performed.

Sort(ascending by Creation Date (or Serial)

# This makes sure you will be grabbing the first created Foreign_ID

GoToRecord [First]

SetField [g_control, Foreign_ID]

# The global is now populated with the current Foreign_ID, which is the first one

Loop

SetField [Foreign_ID, g_control]-no dialog.

# When the Foreign_ID is being "replaced", the lookup will be triggered or not according to the content of the global field.

GoToRecord [Next - end after last]

End Loop

There are situations where you don't want the record to update (example, an old adress in an old Invoice). The global could therefore be substituted by any other locker calculation.

[raymanj]

So there is no way to stop a lookup field from re-looking up automatically when the invoice's CustomerID is filled with new data.

This sucks. Now I have to consider re-modifying all my lookup field to none one and just populate them by script.

Filemaker should build a script function that does a setfield, so that, any related lookup field will not relookup, if specified.

Thanks

[Ugo DI LUCA]

Would you mind have a second look to my answer ? Sorry if it wasn't clear, but that's currently what I explained, I think.

And may be look into the define field options for your Lookup field. Plenty of other buil-in functions.

[raymanj]

Sorry, maybe I didn't understand what you posted above.

I don't see how it stops the lookup fields from re-looking up, when the match field changes. How does the g_control or c_TriggerLookupKey fields stop a lookup from happening. I tried it and the lookups still occured.

All I want to do is change the CustomerID (match field in Invoices) to a new value, and have all related lookup fields not change. This should only happen for this one script that I run. All the other times that I edit the CustomerID field should still trigger all the lookups, which populate the fields (address, phone, discount, etc.) of the invoice.

To make this easier for me, maybe you should leave out the looping parts and just do it for one invoice record. Then I can apply that to a looping script when I need to do multiple records.

Here are some example records:

Customer.fp5 file

CustomerID

NameCustomer

PhoneCustomer

DisCustomer

Invoice.fp5 file

InvoiceID

CustomerID (match field, relationshipe CustomerID::CustomerID)

Name (lookup field based off of CustomerID match field)

Phone (lookup field based off of CustomerID match field)

Discount (lookup field based off of CustomerID match field)

The only other alternative that I am considering is to not have any lookup fields in Invoices. Just have a script that will grab the data out of the Customer file and populate the fields in invoices. Now to me this seems like a lot of wasted time to let a script do, when 99% of the time I want the data to be copied over from the customer file into the invoice file. The other 1% not.

Thanks for your patience Ugo DI LUCA.

[Ugo DI LUCA]

raymanj said:

All I want to do is change the CustomerID (match field in Invoices) to a new value, and have all related lookup fields not change. This should only happen for this one script that I run. All the other times that I edit the CustomerID field should still trigger all the lookups, which populate the fields (address, phone, discount, etc.) of the invoice.

OK, I assumed you wanted to trigger a re-lookup for those duplicate records only.

All you need then is :

g_locker (global number)

c_TriggerLookup --> choose(g_locker,Customer_ID, Customer_ID&" 0"

See attached a little rough test with a duplicate Customer_ID and some related invoices.

Now, you should also prevent duplicates to be entered. Check the Forum for this topic, largely discussed.

lookuplocker.zip

[raymanj]

I understand now. Thank you very much. The example file was an extra bonus but it solidified the concept for me. Now I can easily implement this method.

Oh, did I say thank you? Well thanks again Ugo DI LUCA.