9 Replies Latest reply on Feb 26, 2014 2:17 AM by nass3380

    Report for multiple tables

    nass3380

      Title

      Report for multiple tables

      Post

           Hi,

            

           I created a database to help me with my farm. 

           I have a relation 

           actions to be taken >-- Product --< Production batch

           The actions to be taken table have a field "days" representing the number of days after plantation at which the action should be taken

           I'm trying to create a report where it will show me by date the actions that should be taken for every "Production Batch" (these will depend on the begin date of the production batch).

           Can someone suggest an idea (i'm using filemaker pro 11)

            

           Thank you

        • 1. Re: Report for multiple tables
          philmodjunk

               There is no relationship in your data model that links a specific production batch to a specific group of actions to be taken. You might have two production batch records that link to the same product. That one product could link to 20 actions records, but there is nothing in your relationship to link, say 15 of those action records to one production batch and the other 5 to the second production batch.

          • 2. Re: Report for multiple tables
            nass3380

                 Actions are general for every product that i plant

                 Once this product is planted a new production batch is created.

                 I'm trying to make a report where it will show by date (depending on the plantation date in production batch) what are the actions that should be taken.

                  

                 Actions te be taken table         product Table            production batch table

                 productid (num)>----------productid(serial)-----------<productid (num)

                  

                 I'm new to filemakerpro :) 

            • 3. Re: Report for multiple tables
              nass3380

                   All 3 tables have the same prodcut id number (serial).

                   Should i change the relations of the tables for this to work?

              • 4. Re: Report for multiple tables
                philmodjunk

                     You need some way to link your records in Production Batch to your records in Actions. Let me provide a more detailed example of why your current data model that links everything by Product ID has issues:

                     You create a production Batch record with Product ID = 345. Just by your relationships, you can also create a second production Batch record with Product ID = 345. you then go over to Actions and create 5 records. Each are linked to the same Product, Product ID = 345.

                     Now how does the database determine which Action records should be linked to which Production Batch records? They all have the same Product ID so there is no way to determine, say that three of the action records go with the first Production Batch Records with Product ID = 345 and that the other two should go with the second production Batch record that also has Product ID = 345.

                     PS. you may find yourself saying, but there will only be one production batch record for a given product! That might be the case for this growing season, but what happens after you have used this database for several growing seasons?

                • 5. Re: Report for multiple tables
                  nass3380

                       The actions are defined for every product and won't change over time:

                       for example let's suppose that the product is tomato

                       the actions related to this product are

                       - action 1 spray pesticide x after 5 days of plantation, quantity=100 for every 1000m2

                       - action 2 give nutrient y after 10 days of plantation, quantity= 200 for every 1000m2

                       - etc...

                       Now when i decide to plant tomatoes on lets say

                       - 2000m2 on 1/1/2014 i create a new production batch and link it to the product tomato (production batch 1)

                       - 3000 m2 on 1/2/2014 i create a new production batch and link it to the product tomato (production batch 2)

                       i'm trying to create a report that will tell me that on 

                       - 1/1/2014 + 5 days i have to spray pesticide x quantity = 200 on production batch 1

                       - 1/1/2014 + 10 days i have to apply nutrient (quantity=400) on production batch 1

                       - 1/2/2014 + 5 days i have to spray pesticide (quantity = 300) on production batch 2

                       - etc....

                       for sure the product is not only tomato...

                       Sorry for taking your time with this :)

                        

                  • 6. Re: Report for multiple tables
                    philmodjunk

                         As some one who grew up on and still lives on a farm, I'm having trouble with "won't change over time", but I'll take your word for it.

                         There's still the problem that I spelled out before: you have multiple production batches that store different planting dates that link to the same list of Actions to be taken. In order for FileMaker to "do the math" and compute actual dates that use the "days after planting" from an Actions record to compute a date from the "date planted" in a specific Production Batch record, it has to be able to get that value from the multiple production batch records for that product.

                         I can think of two options here:

                         1) use a sorted relationship so that the "most recent Production record" is always the record to use for computing the action dates.

                         2) Modify the data model

                          

                    • 7. Re: Report for multiple tables
                      nass3380

                           The won't change overtime "Actions that normally should be taken" are just for guidance and reminders 

                           1) I can use this method (since i have a one to many relationship between "Production Batch" and "Effective Actions". But how do i do that?

                           2) If i create fields (Action 1, Action 2, Action 3....) in the "Product table" (and remove "Actions to be taken table") can I when creating a report not show the empty fields?

                      • 8. Re: Report for multiple tables
                        philmodjunk

                             1) but that's the point, you don't have a one to many relationship between production batch and effective actions. you have a one to many relationship between product and effective actions and another one to many relationship between product and effective batches. That's not the same thing as a one to many relationship between Effective Actions and Production Batches.

                             But do you understand that this report then only works for the Production Batch record for a given product with the largest date? If you schedule two productions batches for "tomatoes", one with a planting date of March 1st and another with a planting date of May 2nd, the method I am describing will let you produce the needed report for May 2nd but not March 1st.

                             An alternative would be to pull up a list of all Production Batch records (can be limited to one product if you want), that have planting dates > than today or the first of this year or some other specified time and you click to select a production batch before the system pulls up the needed report--by using the planting date from this record.

                             2) That's not at all what I am suggesting here. Each action would still be in it's own record.

                        • 9. Re: Report for multiple tables
                          nass3380

                               Ok thank you for your time :)