11 Replies Latest reply on Aug 10, 2009 3:19 PM by philmodjunk

    Creating a Relationship

    TKnTexas

      Title

      Creating a Relationship

      Post

      With the help I received, I have been able to script my import of the check registers.  Works smoothly and cleanly.  I want to add a table of the invoice detail that makes up each check.  The only place I can obtain this information is from the AP Pre-check Register that is printed just prior to printing checks.  It totally matches the detail check register, if we were to select that option to print. However, we only use the summary check register. 

       

      Working off of the premise that there is only one check per vendor per check run, I have created a key field of vendor number PLUS check date.  ZZZZ yyyymmdd, for eleven characters.  I have the calculation set as TEXT.  In the check register file I have it as a TEXT field with a calculation option.  I did this so I could tag it as UNIQUE.  I use a calculation field in the REMIT table.  I would not think this is the problem. 

       

      I have connected the two fields in the relationship tab.  I thought at that point I would see the invoices populate the portal on the check register.  It didn't.  I created a lookup field for the check number in the REMIT table.  That did not work.  I have two windows side by side to view the data in each table.  It looks the same.  If I copy the key from the check register table and do a find in the REMIT table, it pulls the invoices so I know from this the text is the same.

       

      What have I overlooked?  Any thoughts?

       

       

        • 1. Re: Creating a Relationship
          philmodjunk
            

          "I have created a key field of vendor number PLUS check date"

          What syntax?  VendorNumb + CheckDate (incorrect) or VendorNumb & CheckDate (correct)

           

          In any case, in FMP you can now match up multiple pairs of fields instead of using a calculated key. You can set up your relationship in this form:

           

          Table1::VendorNumber = Table2::VendorNumber AND Table1::CheckDate = Table2::CheckDate.

           

          BTW why use a "date" instead of the check number? Surely the same vendor could make purchases on two or more invoices in the same day and this would give you two checks with the same vendor ID and check date.

          • 2. Re: Creating a Relationship
            TKnTexas
              

            I did use correct syntax:  vendnum&" "&year(ckdate)&right("0"&month(ckdate),2)&right("0"&day(ckdate),2)

             

            At the time the PreCheck report runs check numbers have not been assigned yet.  I am trying to use existing spooled reports to populate my tables.  Even in the scenario when there is one invoice on the one check it is not working.  It is not material how many invoices are generated on a given day (that would be invoice_date).

             

            The calculation above should generate as unique a number as the check number in order to complete the relationship.  Just nothing.

            • 3. Re: Creating a Relationship
              philmodjunk
                

              I assume the "check date" is the date the check is written? If so, then a customer that makes a purchase on two or more invoices on the same day will write their check on the same day right? That's the way I'm reading your description of your process anyway and that will defeat your assumed uniqueness here.

               

              In any case, given the complexity of your calculation field, I'd pull up the fields in question side by side an make a very meticulous examination of each. If even one character is different from what's needed for the fields to match values, you'll get zero matching records. Something as simple and subtle as comparing a zero with the letter 'O' or an extra space is all that it will take.

              • 4. Re: Creating a Relationship
                TKnTexas
                  

                These are vendor check checks.  Check date is indeed the date it is written.   Whatever invoices we have in payables the meet the criteria of due dates will be paid.  Uniqueness on the side of the invoices is not relevant. 

                 

                Each check can have multiple invoices.  The Check Table can only have one check number for each vendor on that date.  It is a one-to-many, one check to many invoices. 

                 

                I have actually copied the key field from the Check Table pasting it into the key field of the child-table (invoices), and it has successfully pulled up the invoices.  I create the fields names with the exact same spelling.  CkDate is always the check date in any table.  Vendor_Number is always the vendors' number in all tables.  I can cut/paste the formula from one spot to another to ensure it is correct.  That is why I am so baffled.

                • 5. Re: Creating a Relationship
                  philmodjunk
                    

                  Is this a stored, indexed calculation? If you look at the field in the field definition list and see "unstored" then that would explain the lack of matching records. While you can get away with using a global field or unstored calculation for one side of a relationship (The table you base a layout on), the other half (the table you base the portal on) must be indexed.

                   

                  Are the calculations' return types text?

                  • 6. Re: Creating a Relationship
                    TKnTexas
                       In the Check table VEND_CkDate is defined as Text with a calculated value.  Calculation is made on import.  On the Invoice table it is a calculation field.  Everything is set to store.
                    • 7. Re: Creating a Relationship
                      philmodjunk
                        

                      "On the Invoice table it is a calculation field."

                       

                      Is this calculation defined to return number (the default setting) or text?

                      • 8. Re: Creating a Relationship
                        TKnTexas
                          

                        It is set for Text. 

                        • 9. Re: Creating a Relationship
                          philmodjunk
                            

                          I'm running out of suggestions frankly.

                           

                          How exactly have you set up your portal? Any chance it doesn't refer to the correct table occurrence?

                          • 10. Re: Creating a Relationship
                            TKnTexas
                              

                            I double checked that ... several times.  I am connecting the final check table (there is a scratch table) and the final remit table (again scratch table).  I use scratch tables to clean the data before it is final.  Once it is in final tables, it cannot be deleted.

                             

                            I really do appreciate the time you have given me on this.  This is not the first time I have done relationships or created portals.  I have been working with Filemaker since version 3.  I did not anticipate problems on this piece of my puzzle.  I thought it would be smooth sailing after I licked the import and parsing problems. 

                            • 11. Re: Creating a Relationship
                              philmodjunk
                                 It's probably some picky little detail you're not seeing and I haven't asked the right question to identify. You may need to get someone to physically examine your design and data or you may need to take a break and come back and look at with a fresh perspective.