14 Replies Latest reply on Aug 7, 2015 2:41 PM by philmodjunk

    Unrelated tables error but tables are related...

    AndrewClancy

      Title

      Unrelated tables error but tables are related...

      Post

      So I'm trying to set a field to be a calculation (see left most window in screenshot) that takes into consideration values from another table (though I think in the screenshot it may erroneosly be "Ingredients Used1" instead of "Quantity Used 1" as it should be) however filemaker continually gives me a "This field comes from an unrelated table" error, but the tables are clearly related! (Right side of screenshot)

      Any ideas?

      Screen_Shot_2015-08-05_at_3.57.51_PM.png

        • 1. Re: Unrelated tables error but tables are related...
          GisaelGomez

          Andrew, double check that your context is the right one. Your are showing relation between tables "Ingredient 4" and "Batches 4" but the context of your calculation is based on "Ingredients" check that.

          • 2. Re: Unrelated tables error but tables are related...
            philmodjunk

            Actually they are not related. Tables are never actually related. Table Occurrences are linked in relationships. Your calculation is specified to evaluate from the context of the Ingredients table occurrence. A table occurrence box with this exact name is not shown in your relationship graph. It then refers to another table occurrence box named Batches. and no such table occurrence is visible with that name either. I predict that if you find the table occurrence box named Ingredients and the one named Batches, you will find that those particular table occurrences are not linked to each other in a relationship.

            For more on Table Occurrences and how to use them, see: Tutorial: What are Table Occurrences?

            • 3. Re: Unrelated tables error but tables are related...
              AndrewClancy

              Well I tried again, relating IngredientsID and BatchID and relating Original Total Quantity and Quantity Used 1 in both table's occurrence 5, but the same glitch keeps coming up. I must be misunderstanding something very much, because I programmed the calculation as "Ingredients 5::Original Total Quantity - Batches 5::Quantity Used 1" and also tried "Original Total Quantity - Batches 5::Quantity Used 1" but the same error message continues to be shown. Any idea what i'm doing wrong?

              • 4. Re: Unrelated tables error but tables are related...
                AndrewClancy

                Ok so after re-reading PhilModJunk's comment for a fifth time I see that their is an actual option to tell the script what table's context to calculate it from. The error has gone away, though the calculation doesn't seem to be actually taking the other value into account yet. 

                • 5. Re: Unrelated tables error but tables are related...
                  philmodjunk

                  Both the calculation and the context must refer to the correct Table Occurrence. In neither place are you specifying a table even though that's how they are labeled in FileMaker. In both places, you are specifying a table occurrence. Each "box" in the relationship graph is a table occurrence and it has a unique name. In your calculation, reference to Ingredient::FieldName and a reference to Ingredient 5::field name both refer to the same table, but each references a different relationship between the "context" table Occurrence and the one named in the calculation.

                  So you need to look at your calculation and make sure that it's referring to the correct table occurrence.

                  For more about table occurrences and how to use them see: Tutorial: What are Table Occurrences?

                  If you are sure that you have your references correct, are you setting up an auto-enter calculation or a calculation field? There can be update issues auto-enter calculations for existing records and also for calculations that refer to related tables as is the case here.

                  • 6. Re: Unrelated tables error but tables are related...
                    GisaelGomez

                    Told you, it was just a problem of context.

                    • 7. Re: Unrelated tables error but tables are related...
                      AndrewClancy

                      I am fairly certain I have my references in order at this point. I'm using a calculation field with the formula: "Ingredients 5::Original Total Quantity - Batches 5::Quantity Used 1" and for some reason it's returning "Ingredients 5::Original Total Quantity".

                      • 8. Re: Unrelated tables error but tables are related...
                        AndrewClancy

                        That is, it's showing the numeric value of the field "Ingredients 5::Original Total Quantity"

                        • 9. Re: Unrelated tables error but tables are related...
                          philmodjunk

                          And from what context and what do your relationships look like now? (you've been making some changes so let's be sure we are all operating from the same "context".)

                          If Original Total Quantity is a summary field, it will compute a total over all records related to a record in the table where you defined this calculation. Quantity Used 1, on the other hand sounds like a number field. If so, it will only reference a value from the "first" related record in batches for a given record in the table where you defined this calculation. Perhaps there is no related record,the field is zero, or it is empty in the first related record.

                          • 10. Re: Unrelated tables error but tables are related...
                            AndrewClancy

                            Current Total Quantity in Ingredients 5 is related to Quantity Used 1 in Batches 5. IngredientsID and BatchID are similarly related. Current Total Quantity is being calculated in the context of Ingredients 5 and and it's intended purpose is to (eventually) take into account all the Quantity's Used by a Batch of the particular Ingredient, subtract them from Original Total Quantity (which is a number to show how much of something was originally ordered/delivered to the client) so that the client can tell how much of an ingredient they currently have so they now win to buy more. The related records that house Quantity Used have Quantity Used 1 filled in, so I would've thought that "something" at least would happen to the end result of Current total Quantity. 

                            • 11. Re: Unrelated tables error but tables are related...
                              philmodjunk

                              Current Total Quantity in Ingredients 5 is related to Quantity Used 1 in Batches 5. IngredientsID and BatchID are similarly related.

                              That doesn't really make sense to me. You can't relate fields. You relate tables. Can you upload a screen shot of the relationships graph showing the Ingredients 5 and Batches 5 relationshiP?

                              Copy and pasting the current exact calculation expression in your next post would also help.

                              The related records that house Quantity Used have Quantity Used 1 filled in, so I would've thought that "something" at least would happen to the end result of Current total Quantity. 

                              My guess is the currently there are zero related records due to problems with either the defined relationship or the values in your match fields.

                               

                              • 12. Re: Unrelated tables error but tables are related...
                                AndrewClancy

                                Sorry for the lack of clarity in my posts- I'm a bit of a newbie to Filemaker and I'm afraid my knowledge of the terminology isn't 100% there yet. 

                                The calculation for Current Total Quantity(being evaluated from the context of Ingredients 5) is: Ingredients 5::Original Total Quantity - Batches 5::Quantity Used 1

                                • 13. Re: Unrelated tables error but tables are related...
                                  AndrewClancy

                                  Alright so apparently simplifying the relationship (I hope that's the right term to apply here) to simply "IngredientsID = BatchID" lets the  calculation work. I'm not sure why it didn't let it before, but I'm glad it's fixed now. I believe that about wraps up this particular issue for me. Thank you all for your help!

                                  • 14. Re: Unrelated tables error but tables are related...
                                    philmodjunk

                                    Until, I scrolled down and saw that you've already done so, that was exactly what I was going to tell you to do.

                                    The original relationship couldn't work. Take a look at the details of the Relationship:

                                    IngredientsID = BatchID AND
                                    Current Total Quantity = Quantity Used 1

                                    That means that for a given record in Ingredients 5, the only records from Batches 5 that would be accessible via this relationship would be records in Batches where the value of Quantity Used 1 is exactly the same as Current Total Quantity AND where the value Of INgredientsID is exactly the same as BatchID.

                                    So that's a relationship that really didn't make sense