4 Replies Latest reply on May 29, 2015 2:44 PM by Fred_3

    Trouble With Related Tables

    Fred_3

      Title

      Trouble With Related Tables

      Post

      I posted an issue related to this and I thought I found a fix.  But now that we are into heavy use, problems are starting to arise.  We have 2 tables (CUSTOMERS, and PRINT TABLE).  The PRINT TABLE table is created automatically through calculation.  The tables are related through a RECORD ID.  The CUSTOMERS table RECORD ID is indexed and autocompleted using GET(RECORDNUMBER).  The PRINT TABLE table RECORD ID is unstored and autocompleted using GET(RECORDNUMBER).  And there is a script operating from a field trigger that adds records to the PRINT TABLE table for the fields to filled in:

      Set Variable [ $RecordNumber; Value:Get ( TotalRecordCount ) ]

      Go to Layout [ “Print Table” (Print Table) ] Go to Record/Request/Page [ Last ]

      If [ Get ( TotalRecordCount ) > $RecordNumber ]

               Delete Record/Request [ No dialog ]

      Else If [ Get ( TotalRecordCount ) < $RecordNumber ]

               New Record/Request

      End If

      Go to Layout [ original layout ] 

       

      The problem we have is when a record is deleted the tables no longer align or matchup.  I have thought about giving up and merging the tables, but the CUSTOMER table is large enough with essential, non-duplicate information.  The PRINT TABLE table is duplicate information from the CUSTOMER table setup in different ways to help with all the various printing layouts.  It really makes sense to keep them separate, but now I am at my wits end trying to get it all to work.

      Any help or advice would be greatly appreciated.

      Thanks.  Fred

        • 1. Re: Trouble With Related Tables
          nolak37

          Hi Fred,

          I have read your problem and would like to help you find a solution however there is a lot of confusion with the information provided.

          What exactly is this database supposed to do? (whats the print table for is basically what i'm asking) I ask because I just don't understand why have 2 tables with the same information, you can just create different print layouts for your different print needs, if needed you could create another table occurance of your customer table allowing you to create relationships to display the data differently.

          If you can explain a bit more about your solution you might find others able to help you out as well.

          • 2. Re: Trouble With Related Tables
            Fred_3

            Thank you Yoshi.

            The PRINT TABLE is more than just duplicate information of the CUSTOMERS table.  It is all autocompleted fields through calculation, but the calculations are all very detailed, i.e. a simple date field in the CUSTOMERS table yields 6 different date fields in the PRINT TABLE with years added, IF statements, etc.  Merging the 2 tables makes the primary(?) table (CUSTOMERS) really big and messy. 

            You're correct.  A more simplistic way to resolve this is with different print layouts, but I have not been able to add IF statements to print layouts.  If that is possible, than this original problem goes away.

            • 3. Re: Trouble With Related Tables
              philmodjunk

              The CUSTOMERS table RECORD ID is indexed and autocompleted using GET(RECORDNUMBER).

              That won't work. You need an auto-entered serial number.

              All Get ( RecordNumber ) returns is the postion of the record in the current found set. IF this is a stored calculation field or auto-enter field, it will be the position of the record at the time that it is first corrected. Simply sorting your records, performing a find, omitting records will all change the value returned by Get ( RecordNumber ).

              • 4. Re: Trouble With Related Tables
                Fred_3

                Thank you Phil.  The auto-entered serial number was what I needed.