13 Replies Latest reply on Jan 12, 2017 2:45 PM by Flyfisher2611

    A comparison Report between two tables!

    Flyfisher2611

      Hi

       

      I have two tables

       

      1. A label Bin Table (each bin has an ID A1, B2 etc) each bin contains plant labels! - Parent

       

      2. A Plant Scheduler table, showing Various varieties of plants I am growing this year. Each variety is linked to a specific Label bin, so we know where to find the labels! - Child of Label Bin table and Plant Detail table.

       

      3. A Plant Details table, listing lots of varieties of Plants, some are being grown this year but some are not.

      Some of the varieties not being grown still have a label bin assigned to them (even though the bin will not be used this year!) - Parent

       

      What I need to do is create a report to list all the Label Bins that DO NOT have any of this years plant varieties allocated to them, in other words, I can see a list of the obsolete Varieties/Label Bins.

       

      This would then give me the ability to easily assess whether to remove the variety from the Bin No. as we are unlikely to grow it ever again or leave the variety assigned for use next year. As you may guess, I am short of Label Bins and need to make some space!

       

      Thank you in anticipation

       

      Kind Regards

       

      Alan

        • 1. Re: A comparison Report between two tables!
          philmodjunk

          Does your plant scheduler table have a year or date field such that you have records from last year's schedule as well as this year or just the records for the current year?

           

          And is there only one label to a bin--thus defining a 1 to 1 relationship between a scheduled plant and it's label bin?

           

          Or could there be more than one label in a bin?

          1 of 1 people found this helpful
          • 2. Re: A comparison Report between two tables!
            Flyfisher2611

            Hi Phil

             

            No there are no date fields.

            The Schedule table only has 2017 plants, so just the current year.

             

            Good point about the label bins, some do have more than 1 variety assigned

            to the bin. Maybe 10% have multiple varieties (up to four) the rest are

            single varieties!

            • 3. Re: A comparison Report between two tables!
              philmodjunk

              Then your relationship between the schedule table and the label bins table is one to many from bins to schedule.

               

              LabelBins::BinID = PlantScheduler::BindID

               

              Exact names may differ but you get the idea.

               

              Then the following method can be done manually or in a script.

               

              Have a layout based on LabelBins that lists the fields from Bins that you need. Add the PlantScheduler::BinID field to this layout if you do this manually.

               

              Enter find mode

              Put an asterisk into the PlantScheduler::BinID field

              Select the "omit" option

              Perform the find.

               

              This will produce a list of all BinLabel records that do not have a related record in PlantScheduler. Should you decide to add a date or year field and keep records from past/future years in the scheduler table, this technique can still be used, but you'd need a relationship that includes a global field in the  BindLable field that specifies the desired year.

               

              Let me know if you need help with a scripted find that would set up such a report for you.

              1 of 1 people found this helpful
              • 4. Re: A comparison Report between two tables!
                Flyfisher2611

                That's great Phil, I will have a go at this tonight and let you know how I

                get on!

                 

                Thank you!

                 

                Alan

                • 5. Re: A comparison Report between two tables!
                  Flyfisher2611

                  Hi Phil

                   

                   

                  I am getting close I think but the relationship is actually on 'Variety'

                   

                   

                  I'm not really sure how to explain it so I have attached my file for you to

                  see first hand.

                   

                  I say I am close because I can actually find the bins I need (the

                  obsolete bins) the problem is that the variety field in the report is blank

                  and I don't know why! The reason it is not a simple ID field join is

                  because I have used various conditional drop down fields.

                   

                   

                  I would be grateful if you could have a look through it, at your

                  convenience. To see the report

                   

                  To navigate to the report Main Menu >

                   

                  Main Menu > Label Bin Menu > Obsolete Bins (I haven't done the find script

                  yet, so have to perform manual find)

                  • 6. Re: A comparison Report between two tables!
                    philmodjunk

                    As long as the relationship correctly matches each scheduled plant to the correct Label Bin record, this should work.

                     

                    And the match field from scheduler SHOULD be empty. That's because your unused label bins don't link to any record in the scheduler table.

                    1 of 1 people found this helpful
                    • 7. Re: A comparison Report between two tables!
                      Flyfisher2611

                      Arhh!

                       

                      Then its working! The problem was that I couldn't read the variety that was

                      in the bin, however, I have put another variety field from the Plant

                      Details table over the top of the 'find' field, for the purposes of the

                      report!

                       

                      Fantastic!

                       

                      Thank you, once again for your time and effort

                       

                      Kind Regards

                       

                      Alan

                      • 8. Re: A comparison Report between two tables!
                        Flyfisher2611

                        Hi

                         

                        sorry! I was hoping not to bother you again,

                         

                        im having a problem creating a script to run the find, i.e. populate the variety field with an asteris and omit the appropriate records. I have tried 2 ways one by creating and running a saved find, and the other creating and running a script.

                         

                        many thanks

                         

                        Alan

                        • 9. Re: A comparison Report between two tables!
                          erolst

                          Flyfisher2611 wrote:

                           

                          im having a problem creating a script to run the find, i.e. populate the variety field with an asteris and omit the appropriate records.

                          If I've read Phil's suggestion correctly, your script would need to look like:

                           

                          Enter Find Mode

                          Go to Layout [ LabelBins_list ( LabelBins ) ]

                          Set Field [ target field: PlantScheduler::BinID ; calculated value: "*" ]

                          Omit Record

                          Set Error Capture [ on ]

                          Perform Find

                           

                          Adapt to your actual layout, table and field names.

                          • 10. Re: A comparison Report between two tables!
                            Flyfisher2611

                            Hi Phil

                             

                            Thank you once again!

                             

                            I wasn't a million miles away, apart from I had selected 'perform find'

                            instead of 'enter find mode'!

                             

                            The script pauses and I have to physically click 'perform find' is that

                            normal?

                             

                            I had not included 'Set Error Capture on' not least because I don't know

                            what it does, could I trouble you for a brief description?

                            • 11. Re: A comparison Report between two tables!
                              erolst

                              I'm not Phil ...

                               

                              Anyway, you can deactivate the 'pause' option for Enter Find Mode.

                               

                              And then there's always the documentation: FileMaker Pro 15 Help

                              • 12. Re: A comparison Report between two tables!
                                Flyfisher2611

                                I do apologise erolst! I replied via my email account rather than the site

                                and I never even looked, I just assumed it was Phil

                                 

                                I realise you can deactivate the pause option but it is still pausing!

                                • 13. Re: A comparison Report between two tables!
                                  Flyfisher2611

                                  I had inadvertently deleted 'Perforf Find' !!!

                                   

                                  Sorted

                                   

                                  Thank you both for your help!