Calculation Fields/AutoRecord creation not triggerring relookups?

[NRT.Rob]

Hi All,

I have two occurances of something that seems to be a related problem. This is in a newly converted FMP8 database, used to be FMP5.

I have an orders database, viewing orderlineitems through a portal. Portal lines are displayed by matching ordernumbers, and is set to create new records in orderlineitems via the portal (i.e. we can just type new part numbers into new portal lines and records are created).

If I go into orderlineitems after creating the line items, the order number IS there in the line item match field.

Now: I have a calculation field in line items that, on a new order number entry, will equal the orderBillToID number FROM the order (which is a hand entered value). Thus, this field should always equal the bill to's unique customer ID number, even if we change it after the order is created.

The problem is; it doesn't seem to be triggerred to calculate when we enter new records now. I've tried comitting both Orders and OrderLineItems, and nothing works except hand-entering the order number in the line items, or doing a "relookup" based on order number. Not what I would expect from a calculation field, it seems to be behaving more like a lookup! I could of course script a relookup command, but have no way to trigger it except perhaps through a button that the user would have to use after many any adjustments to the orders to "verify changes" or something. Not clean and Not safe.

Secondly, a similar problem seems to be happening with a another field in the same lineitem database.

Once a billtoID is found, a CustomerMultiplier value is set as a lookup; it looks up a value using a double relationship to another table, where if billtoID and customerID match, AND manufacturer matches, it looks up that customer's price multiplier for that manufacturer.

However, even when I relookup the order number to pull in the billtoID, that does not trigger the lookup. I have to then relookup the manufacturer (which is itself a value that does look up properly when we put the part number in a portal line item) to get the multiplier lookup to work.

It appears to me that for some reason, now calculation fields are executing just like they were lookups instead of being the real-time "correct" values I expect from calculations. At least, calculation fields based on relationships.

It also seems that a change in a calculated value will no longer trigger relookups based on that field.

Is this true?

Do I have to rewrite this whole system to work in FMP8?

Or am I just missing something?

Thank you very much in advance for any help you can offer.

Edited May 27, 2006 by Guest

[IdealData]

I did a 5 to 8 conversion recently and I had problems with value lists (from related files). I had to set the indexing to "on" for the key fields for them to work as the conversion process had failed to retain the original indexing status.

It might not be the answer to your problem, but it's pobably a place you haven't look at yet.

Other than this I suggest you isolate the problem by making a new file(s) - NOT CLONES - (in the same style as the current ones) to replicate the features and see if the behaviour persists.

HTH

[NRT.Rob]

I actually checked that, but since billtoID in the order line items is a calculation field based on a relationship, it won't let me turn indexing on. A feature I have always found to be particularly aggravating, I might add :

I'm doing all my testing on copies until I find fixes, then fixing the original source files with the verified fixes.. when I'm done, we'll do the final data import from FMP5. Thanks for making sure though!

[NRT.Rob]

Hmm, this is fun. If I change billtoID to a lookup field instead of a related calculation, then it works perfectly; so I can't even say that the calculation field is "behaving like a lookup", strictly speaking.

that also fixes the second problem, as the multiplier lookup is now dealing with two "real data" fields and not calculations, and appears to be properly triggerred by the lookup fields.

However, still not ideal, since now I have to add some sort of button to relookup my order line items if a billto is changed. ah well I guess. Still if anyone knows what the problem was with autoentered values and lookups not working with the related calculation, I'd love to know what's up!

As it is, I'm afraid my data import just got messier : I was hoping to avoid any field changes like this.

[Razumovsky]

It also seems that a change in a calculated value will no longer trigger relookups based on that field

I believe it is that a change in a calculated value BASED ON A RELATED FIELD will not trigger relookups based on that field. I thought this was the same with FM5 though...

Perhaps you could post a sample?

-Raz

Edited May 27, 2006 by Guest
read closer

[NRT.Rob]

Hmm, that might be right, because it occurred to me in FMP5 if we did change the bill-to ID on the order, we did have to relookup the line items by hand (not a common occurance, you might surmise!).

However, what is different now is that in FMP5, the INITIAL creation of a line item would execute the calculation. Now in FMP8 it would not, and when I finally got the billtoID to calculate (say, after I went to define databse and came back), the rest of the calcs still didn't relookup.

I'd post the example, but these DBs are so heavily related to the rest of my system (inventory, job tracking, contacts, etc etc) that I'd pretty much have to post my entire 24 file solution...

It's ok for now. I guess I'll just have to watch myself and try to avoid using related calculations as lookup key fields or something..