1 2 Previous Next 17 Replies Latest reply on Mar 1, 2011 2:13 PM by philmodjunk

    Create Report using 2 Tables

    LarryMadden

      Title

      Create Report using 2 Tables

      Post

      I am new to Filemaker Pro and I am trying to create a report using 2 tables.

      I am trying to build a mini warehouse database.  I am trying to create a report based on 2 tables (contract & ware#).  The contract table draws info from several tables including Ware#.  It is the main table in which information is collect to create a contract between the renter and the owner.

      I am wanting create a report that shows all vacant warehouses.

      In the contract table, I am using the following fields:

      Contract Date, Client Name, Warehouse #, Termination Date (There are other fields but I thought these were the only ones necessary, really don't even need Client Name).

      What I was thinking was:

      1) If Warehouse # has an associated contract date and no termination date, then the warehouse # is not vacant.

      2) If Warehouse # is not in a contract, then it is vacant.

      3) If Warehouse # has an associated contract date and does have a termination date, the unit is vacant.

      I suppose a query needs to be run on the contract table to find all Warehouse #'s that have a Contract Date and no Termination Date and then compare to the Ware# table that includes all units to find the vacant units.

      I don't know how to do this.

      Any help would be appreciated.

        • 1. Re: Create Report using 2 Tables
          philmodjunk

          How have you related the two tables?

          Like this?

          Warehouse::Warehouse # = Contracts::Warehouse #

          You need to perform a find on Warehouse as you need to find all warehouse records that meet either criteria 2 (no contract) or 3) Contract has Termination date < today's date.

          It depends on whether or not Contracts contains multiple (past) contracts for the same warehouse or not. If it doesn't, simply search for <= today's date in the termination date field from your warehouse based layout.

          If you do have multiple contracts records for a given warehouse, here's an approach that will work in that case:  Define an unstored calcuation field, cVacancy in Warehouse as Last ( Contracts::Termination Date ) < Get ( CurrentDate )

          Clear the "do not evaluate if all referenced fields are empty" check box and search for 1 in this field.

          • 2. Re: Create Report using 2 Tables
            LarryMadden

            Thanks for the reply.

            Yes the tables are related with the Warehouse # field.

            Each warehouse could have multiple contracts associated with it (one renter terminates and then a new renter rents)

            Not sure how the unstored cVacancy field will give the complete list.  This will just give me the warehouses with termination dates correct?

            Sorry so ignorant.

            • 3. Re: Create Report using 2 Tables
              philmodjunk

              If there is no contract at all, Last ( Contracts::Termination date ) returns null. With "do not evaluate..." cleared, the expression then returns True (the number 1) as null < Get ( CurrentDate ) evaluates as a true statement.

              If there is a termination date, the expression only returns true if the Last related Contract record's termination date is less than today. Thus it works for both situations. This approach assumes that the relationship between these two tables are either unsorted (the default) or are sorted in an order that makes the most recent contract record for a given related warehouse record the last warehouse record.

              • 4. Re: Create Report using 2 Tables
                LarryMadden

                What kind of field would cVacancy be?

                I guess I then create a query for "1" in the cVacancy field and then create a report based on the results of the query.  Since I am new to FMP, I'm sure I will need some hand holding through this process.  I hope you don't mind.

                Thanks again for your assistance.

                • 5. Re: Create Report using 2 Tables
                  LarryMadden

                  I see the field answer "calculation".  Sorry for that.

                  • 6. Re: Create Report using 2 Tables
                    philmodjunk

                    Field type calculation with a return type (drop down inside specify calculation dialog) of number.

                    • 7. Re: Create Report using 2 Tables
                      LarryMadden

                      I created the cVacancy field as you described.

                      When I look into the warehouse table (ware#), each unit comes back with a "1".  Even the units that have contracts made for them.  What do you think I am doing wrong?

                      Larry

                      • 8. Re: Create Report using 2 Tables
                        philmodjunk

                        Can't tell much from here.

                        Even the units that have contracts made for them.

                        What are there termination dates? It should return 1 if the most recent contract record has a completion date < today's date.

                        • 9. Re: Create Report using 2 Tables
                          LarryMadden

                          None of the units have termination dates.  I have entered in sample contracts and they all have beginning contract dates but no termination dates.  I figured those would return a "0" in the cVacancy field.

                          • 10. Re: Create Report using 2 Tables
                            philmodjunk

                            Muy Bad, Empty termination dates will also evaluate as true--which means this won't work as designed.

                            IsEmpty ( Last ( Contracts::Warehouse #) ) or 
                            ( not IsEmpty  ( Last ( Contracts::Termination Date ) ) and ( Last ( Contracts::Termination Date ) < Get ( CurrentDate ) ) )

                            • 11. Re: Create Report using 2 Tables
                              LarryMadden

                              The last code worked.  Thanks.

                              Now here is where you know that I am new.

                              I started a new report based on the Warehouse data.  I know I need to use the cVacancy as the filter.  Show only units that have a cVacancy = 0.  Could you also give me some pointers on how to do this.

                              Thank you so much for your help.

                              • 12. Re: Create Report using 2 Tables
                                philmodjunk

                                You can perform a find either by hand or in a script to bring up the records you want. You can use a script trigger to perform this each time you select this layout by using the onLayoutEnter script trigger.

                                A scripted find for this:

                                Enter Find Mode[] //clear the pause check box
                                Set Field [Warehouses::cVacancy ; 0 ]
                                Set Error capture [on]
                                Perform Find[]
                                Sort [no dialog restore]

                                • 13. Re: Create Report using 2 Tables
                                  LarryMadden

                                  I tried to enter what you had verbatim but it wouldn't let me type your code in.  I created a script named "Vacancy Report" when I created the vacancy report.  FM automatically added code.  I enter your info in and it gave me eventually all the used units (those with a cVacancy value of 0.  I changed your Set Field line from a 0 to a 1.  However, that didn't solve the problem.  Here is my code exactly:

                                  Go to Layout ["Vacancy Report"(Warehouses)]

                                  Enter Find Mode []

                                  Set Field [Warehouses::cVacancy; 1]

                                  Set Error Capture [On]

                                  Perform Find/Replace [Warehouses::cVacancy = 1; Find Next]

                                  Sort Records [Restore; No dialog]

                                  Enter Browse Mode []

                                  If [GetAsNumber ( Substitute ( ApplicationVersion ); "."; "x")) < 1000]

                                  Enter Preview Mode [Pause]

                                  Enter Browse Mode []

                                  Go to Layout [original layout]

                                  End If

                                  Can you help me?  Sorry for being a novice.

                                  • 14. Re: Create Report using 2 Tables
                                    LarryMadden

                                    I removed my perform find replace statement and added the perform find statement you wanted.  It works great.  Thanks.

                                    1 2 Previous Next