FileMaker 8, 9, 10, 11, 12, and 13 all use looked up data the same way and should function the same.
Here's what that entry means.
Say you have a calculation field named FieldA + FieldB. If you put the cursor in this field, Relookup won't work. But if you put the cursor in FieldA or FieldB, Relookup should work and you should see the contents of this calculation field update.
Still having trouble with it. The calculation field is in another file that is related to to the current file using a field named 'inventory number' . For the relookup to work I specify the 'inventory number' as my target field. It copies over all the other fields but not the calculation fields.....the calc fields are 'unsorted' and not indexed if that matters.
'unsorted' doesn't make sense. Do you mean "unstored"?
And is this a field of type calculation or is it a data field with an auto-entered calculation?
Fields of type calculation should never need to be relooked up as they don't use that feature in the first place. When the fields that they reference are updated, the calculation field should automatically update whether stored or unstored.
But this will not be the case with an auto-entered calculation.
opps. yes unstored.
Type = calculation
So to give you a little more background...and note I didn't design this just trying to fix it for my mom. There are three files involved....an inventory file, and invoice file and a scratch file (a temperary file to just perform an inventory calculation).....
We input inventory quantly into the inventory file, we consume the inventory in the invoice file....but to get the inventory file to decrease as we sell items, we use the scratch file to get the orginal invetory number from the inventory file and subtract the items that are sold from the invoice file.
So when the script goes back to the inventory file and does a relookup from the scratch file of the new qty it won't copy over the calculatioin field that was used to get the 'new inventory qty'.
The files aren't really set up to auto change inventory numbers until you press the script button.....and it's storing data in diferent fields until the relookup script calls for an update. If I change the lookup settting of the inventory qty field to pull from a field other than a calculation field it works. Hope that helps figure out the solution.
Sorry, but it's really not enough info to understand the critical details of your setup.
But if a calculation field does not update after the relookup there really are only two possible reasons:
The fields used to calculate the value did not get updated by the relookup.
Your file is damaged in some way.
I'd check the values of the fields used to calculate the value.
But this phrase also concerns me:
the new qty it won't copy over the calculatioin field that was used to get the 'new inventory qty'.
That suggests that I may have misunderstood the situation.
Where is this calculation field? Is it in the look up table, the table from which values are being looked up or in the table into which the looked up values are being copied?
I have been assuming that the calculation field is defined in the table into which the relookup is copying data. But that last sentence suggests the opposite.
For future reference if you decide to improve on the basic design, you might look at this thread: Managing Inventory using a Transactions Ledger
I've determined that it is updating the qty field in the invetory file but it's just not doing it correctly. The scratch file subtracts the invoice qty from the 'current' inventory qty. All the calcualtions are correct and operating in the scratch file....see attached. but it seems like it's not performing the calculation when relookup is applied in the invtory file.
Then the issue is not with the looked up value settings nor the re-look up. I can't see any "attached" at this moment. Either you've tried to upload a file of the wrong type or it's just being slow to appear. I may see the image after I post this reply...
Now I can see it, the forum was just being slow.
Looking at the field definitions:
New Quantity In Stock is defined as:
Quantity In Stock - Total Quantity by Item Number
But Total Quantity by Item Number is defined as:
GetSummary ( Total Quantity ; Inventory Number )
From the screen shot I see correct calculation results for New Quantity In Stock
First row: Total Quantity By Item Number = 1; Quantity In Stock = 3; 3 -1 = 2--> the value shown in your screen shot
2nd row: Total Quantity by Item Number = 1 ; Quantity IN Stock = 1 ; 1 - 1 = 0 --> the value shown in your screen shot.
Please Note that GetSummary will only return the correct value if the correct records are in your found set and those records are correctly sorted.
I appreciate your help, but I have to leave....and try to finish on monday.
Everything you stated is correct.....but I didn't follow with you think the issue might be now. Like you said everything in the scratch file is correct, but when you hit relookup in the invetory file the number that appears is always the same number that was in the qty field. The obvious mistake is that it is looking at the wrong field but I've checked 100times that it is looking at the 'new qty in stock' field in the scratch file.
I couldn't see any issue in the last post that you made. Everything there evaluated correctly.
But now that I understand that that data looked up FROM this file has an issue, I will suggest that a calculation field that uses GetSummary is not a good choice for a field from which to look up data. That calculation evaluates at the layout level--found sets and sort orders will determine what value is returned, but looked up value settings copy over data at the data level so I wouldn't expect that calculation to be a reliable source of data to look up.
Fixed. I ended up adding a new calc field.....new qty = quantity in stock - quantity. and everything worked when I used this field as the lookup field. this one field has replaced the 'total quantity', 'total quantity by item number', and 'new quantitiy fields'. With my limited knowledge of how this table worked, I was worried about changing these fields too much because it appeared that with how it imports all records to do the original calc that it was doing something special to track it all by inentory item and this was the reason for having a field ' total qty by item number'. but my new calc seems to work.
Also, I did notice that with my above screen shots it wouldn't perform the calculations until it got to a sort command in the script.....so in the end the relookup needed to 'commit' or 'sort' again before grabbing the data for the old way to work.
and thank you.