Passing Lookups through relationships

[LaRetta]

Hi everyone,

I need help understanding how FileMaker processes the following lookup conditions ...

Structure:

Contacts to Invoices (on ContactID, standard text) using =

Invoices to LineItems (on InvoiceNo, standard text) using =

I need to lookup the Contact field SalesRep into both Invoices AND LineItems. I know it appears redundant but there are various SalesReps which may add onto an existing Invoice and their payments adjusts accordingly. Sales Reps trade Contacts like baseball cards. So the control of charging out an Invoice or LineItem is determined by who possesses a Contact at the moment an Invoice is created and also at the moment each LineItem is added.

Originally I set LineItems SalesRep to lookup directly from Invoices SalesRep. Invoices SalesRep is also set to lookup from Contacts SalesRep. The Invoice is created a few minutes before a LineItem is added so I saw no conflict in the lookup timing (ie, Invoice SalesRep field lookup would be finished before the LineItem lookup happened) and it seemed that a direct relationship lookup might be quicker (although I'm still wrestling with that theory). But it ran like a dog. I then changed it to a lookup starting at LineItems and Lookup from related table Contacts; in otherwords THROUGH the Invoices. It is instantaneous.

Here is one difference but I don't understand why: The LineItems to Invoices lookup was instant when executed from the host but took 5 seconds to execute from workstations. The LineItems to Contacts lookup is instantaneous from both host and workstations. Why would that make a difference? And why would it be quicker to pull from a distant table than the one immediately connected?

Can anyone help me understand the logic behind this (apparent) anomoly? Or is there some other piece I'm missing in this dynamic? Thank you!!

LaRetta

[Ender]

I am very suprised by this finding LaRetta. Are you sure you didn't change a validation option or an auto-entered calc on some field while you were tinkering around?

[LaRetta]

Hi Mike,

I am surprised also I repeated this same test several times through the network, ie, by only changing the Lookup popup box on which TO to pull from - no other modifications to the file at all. Lookup from Invoices aross network, dawg. But switching only that popup to Contacts made it work instantly.

Could the number of records have something to do with it? Invoices has 80,000 records, Contacts has 20,000 and LineItems has 500,000. I am lost for an explanation.

We do not have Server Advanced implemented yet (we decided to upgrade all our hardware first). But even so, the results should be consistent, shouldn't they? The only other consideration may be that I'm script-creating the LineItems but again ... makes no sense and I would think they should respond the same way. These are all base TOs without relationship sorting also; as pure a TOG as one can get.

I will simply go nuts if I don't figure out the logic of it.

LaRetta

[Søren Dyhr]

The LineItems to Invoices lookup was instant when executed from the host but took 5 seconds to execute from all workstations.

Isn't it a known issue with networked solutions that something like globals forces the chatting entire indexes to the client, and the same thing could very well be happening if using the index of a smaller number of recorrds.

I wish that Debi Fuchs who made a whitepaper on Packet Watchin for the previous ver. come up with a similar for this version.

Since each invoice line has or might have different SalesRep, why not store this info in with the foreign key, allthough 1 NF is broken slightly, if it's done in two lines pilcrow delimited will no record in the portal turn orphaned!

--sd

[LaRetta]

Thanks for the suggestion, Soren

Well it works quickly - LineItems through Invoices to Contacts (selecting Contacts from Lookup popup) so it's simpler to keep it this way.

So you think it might be because Invoices has 80,000 and Contacts only has 20,000 so the lookup takes longer? 5 seconds longer? Wow. If so, I'll NEVER perform a lookup on my 500,000 record LineItems table! Yikes! And wouldn't a lookup THROUGH Invoices then also take as long? Besides, the power of the relationship limits the number of records - regardless, right? I use that all the time to isolate and constrict a relationship, ie, my LineItems becomes a teddy bear when it's related on several criteria because that is all that is drawn through the relationship. It doesn't even SEE all 80,000 invoices - but only one!

Tomorrow I plan on testing it again. It may be that the slowness exists on only SOME User systems and not others (?) possibly indicating a network configuration or sharing issue (?) but again - why would that make a difference, ie, changing the popup would then speed it up? This is one file only - no other copies of this file exist anywhere on the network or any computer at work (except as zipped copies). Our network itself is a bit wonky which is why we're re-vamping the entire thing.

Each table in the graph indicates data passes both ways and every key is standard text. The ContactID is auto-enter (do not replace) via calculation (which is simple serial). The invoice is auto-enter (do not replace) serial.

Another thing ... LineItem creation is scripted.

I may end up farming this design out to a Developer that can test it for me just to satisfy my buring curiosity. I suspect, as Mike has indicated, that the problem would NOT repeat in another network structure. It makes no sense. But I would like to be sure of this. I wish I knew exactly how this was handled internally by FM. I wish I understood how FM thought.

I'll post if I ever figure it out. Thanks everyone.

LaRetta

[Søren Dyhr]

possibly indicating a network configuration or sharing issue

I took it for granted that it couldn't be the case, that the drive where the solution lives were shared via other systems like OS filesharing??? If you havn't installed the solution the installer might not be aware of that??

But I need a better explanation as to why you need the lookup in the first place - I think I'll toy with my twoline key ...which at least in my teory will work just as well, for the reporting task at hand.

--sd

[bruceR]

For perforrmance reasons it appears that lookups should almost always be replaced by auto-enter calcs, you will get the same resulting value but - many others who have actually tested this have said - much better performance.

[Søren Dyhr]

Another thing ... LineItem creation is scripted.

Indeed it has to be if you use the idea here - I sussed it right, there isn't apparent reason to lookup the SalesRep into the LineItem table, reporting still happens when drawing values a relation away ...perhaps several realtions away. The ID is all it takes.

I might have misunderstood your problem?

--sd

Laretta.zip

[LaRetta]

Hi Bruce,

Thanks for helping. Well it makes sense, I guess. But then why did FM keep lookups? Backward compatibility only? FM could have converted those for everyone during the conversion process and dropped them if of no use but they kept them - why? I realized I could also just set the field when I script-create a new LineItem. Again, I was looking for the cleanest, simplest method and Lookups seem designed for just such a purpose.

I used Lookups because that is precisely their intended purpose, is it not? A simple draw of data through a TOG upon record creation. And I assumed (wrongly?) that it would be a bit more resource efficient than auto-enter calc (although negligible). Lookups are working well, after all, throughout the rest of my solution.

Now I question and doubt everything regarding Lookups and have no idea whether to change them all to auto-enter (do not replace) calcs. And it still doesn't solve the dichotomy of the speed difference (in my situation) on which TO they pull from.

Ah well, time to go to bed. Ooops! Time to go to work instead. Amazing how quickly a night can go by when one is working.

LaRetta

[Ugo DI LUCA]

Hi,

I've heard about lookups being slower with 7 actually. Better use auto-enter calcs indeed as suggested by Bruce, at the moment.

However, it may be related to the way records are now commited with 7, specially if your lookup doesn't fill on the first related record.

With 6, it was good practice to exit the record as soon as the record was created, prior to any entry into the Line Items (from either scripted approach or portal set to allow creation of related records). If this behaviour existed with 6, while it didn't had the heavy commitment procedures we now have with 7, I would think it's now natural that it takes that long.

[Ugo DI LUCA]

Hi again,

Why it happens to be quicker from Contacts, even through the Invoice Table, rather than directly from the Invoice itself may be related to the former statement in fact, as going back through the Invoice Table to the Contact Table is forcing commitment on the Invoice.

That's how I interpret the situation.The same as a Case(MyField,etc.) is refreshing results of a calc based on MyField on the current record.

[LaRetta]

Thanks Ugo!

Okay, I'll change my lookups to auto-enter calcs (do not replace). And I appreciate the additional explanation - that makes sense. Well, I admit I'm a bit disappointed in Lookups now - at least with vs. 7.

And would that explain the time lag between executing the lookup from the host as compared to through the network? Why would it then be instantaneous from the host but take 5 seconds from workstations? Ah well.

LaRetta

[Søren Dyhr]

I feel daft as a brush, why lookups in the first place - why not unstored calc's - is it this old problem???

http://www.dwdataconcepts.com/dl/tm/UPDATE_CALCS.sit

http://www.dwdataconcepts.com/dl/tw/UPDATE.ZIP

--sd

[Ugo DI LUCA]

Hi Soren,

It depends what exactly we're looking up into the Line Items from the Contacts or whatever distant Table.

There would be no need to lookup the contact name into the line items at first glance, but there could be a need to lookup a priceGrid ID that finally determines what the discount the customer should get depending upon the product, the quantity ordered, or whatever.

And this is often a variable parameter which should remain unchanged in the line items once it has been set, rather than a related calculation.

Again, an auto-entered calculation, again, might better fit the need, set to not recalculate.

[Søren Dyhr]

often a variable parameter which should remain unchanged in the line items once it has been set

Ah yes!!! ...But woudn't it be smoother maintenance wise to pass only results over as scriptparameters in stead of making a hedge hog of almost identical relations, which indeed did make sence with previous versions but now could confuse developers taking over the solution. It's much easier to document scriptsteps than autoenter values... MiddelValues( is great for the splitting of the parameters.

Laretta is generating new itemlines by script anyway!!!

--sd

[Ugo DI LUCA]

Soren,

Yes one way would be to use scriptParameters and another would be to pass it directly from globals and multiple keys on each side of a relationship set to "allow creation of related records"

InvNum = invNum

ContactID = contactID

gProductIDSelected = productID

cRelatedPriceGrid_ProductAndCustomer = priceGrid

SetField[LineItems::invNum;Invoice::InvNum]

where cRelatedPriceGrid is a given discount % based on the combo ProductID-CustomerID

[Søren Dyhr]

Unless it's a lookup next higher/lower - it just occured to me! ...or is it done via lookups inside calcs???

--sd

[Ugo DI LUCA]

Hi,

This is currently the case I'm personnaly dealing with in my newest solution, where one can have as many as 6 different price grids depending upon quantity, product, productcategory, manufacturer,promotional items.

I started by defining an auto-enter calc with nested lookup ( ) function and a LookupNext ( ) for the Quantity breaks. It's just instantaneous and recalculate as long as the flag for invoiced is set, so that the user may temporarily change the privileges attributed to a given customer within the line items itself so that he gets a better rebate, and the price evolves according to the input into the quantity field.

Then, by switching to a Pick from list (Shopping Kart) system which was finally a better choice (we can visualize the whole order before it gets "commited", and each picked item brings a window where stock levels may pop-up, etc.), the discount and price are brought to the line items as globals through a multi-equijoin relationship.

Straight and instantan

[iNik]

LaRetta, are your primary keys here the same length and type as one another? If you have on PK/FK pair which is a small integer, and another which is a longer key, that can drastically impact lookup times, especially if you have a text key field.

[LaRetta]

Hi iNik,

" are your primary keys here the same length and type as one another? "

They are identical ... both simple text invoice number (6 characters) Invoices to Lineitems, and ContactID (8 characters) Contacts to Invoices. The field which needs to prefill in LineItems is a straight 5 character text field. It simply couldn't get any cleaner. The ID keys are based upon straight = in every table and are identical data types.

I find auto-enter (do not replace) easier than passing via script (and happens instantly from both host and station) and easier to find and change if need be. With the advent of field comments, it's simple to view the specific purpose although in this case there is no need - as the auto-enter calculation is simply: Invoices::ContactRep

I retested it through the network - only changing the table the lookup pulls from (from the Lookup popup).

LineItems Lookup from Invoices - Host: 0:00:03

LineItems Lookup from Invoices - Station: 0:00:05

LineItems Lookup from Contacts (through Invoices) - Host: 0:00:00

LineItems Lookup from Contacts (through Invoices) - Station: 0:00:02

I believe this is a clear indicator of NETWORK lag and Commit Record issues. It's difficult when talking about such small time increments but it seems like an eternity when staff is entering data. Nope. This girl isn't going to use lookups anymore ...

LaRetta