12 Replies Latest reply on Jan 21, 2014 3:40 AM by johnf_2

    relookup....calculation field

    johnf_2

      Title

      relookup....calculation field

      Post

           I upgraded to FMP 11 and I'm having an issue with a relookup script command.  In the old version it worked fine but it doesn't now.  It looks up a calculation field and in fmp help it said the follow:

           "caluculations fields cannot be used for the Relookup field contents command.  If you use the Relookup field conents command, and you're using a calculation field as the matching value, you must specify one of the input fields for the calculation as the field parameter for the Relookup field contents."

           After reading this I thought it was still possible to relookup a cacluation field but I don't understand this sentence: " you must specify on the input fields for the calcuation as the field parameter for the relookup field contents."

           What I thought it meant was that I need to incorporate the destination field (the lookup field) into the calcuation field.  I did this but it still doesn't work.  My issue is that this looks up values for several recoreds at once and populates accordingly. 

           If someone could help me out and either point me in right direction or explain an entirely new way to do this that would be great.  Thanks.

        • 1. Re: relookup....calculation field
          philmodjunk

               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.

          • 2. Re: relookup....calculation field
            johnf_2

                 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.

                  

            • 3. Re: relookup....calculation field
              philmodjunk

                   '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.

              • 4. Re: relookup....calculation field
                johnf_2

                     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. 

                • 5. Re: relookup....calculation field
                  philmodjunk

                       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

                  • 6. Re: relookup....calculation field
                    johnf_2

                         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.

                    • 7. Re: relookup....calculation field
                      philmodjunk

                           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...

                      • 8. Re: relookup....calculation field
                        philmodjunk

                             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.

                              

                              

                        • 9. Re: relookup....calculation field
                          johnf_2

                               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.

                          • 10. Re: relookup....calculation field
                            philmodjunk

                                 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.

                            • 11. Re: relookup....calculation field
                              johnf_2

                                   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. 

                              • 12. Re: relookup....calculation field
                                johnf_2

                                     and thank you.