4 Replies Latest reply on Jun 18, 2014 5:08 PM by user22862

    Help with setting fields from multiple records

    user22862

      WHAT I HAVE:

       

      • 4 different PDF's that share some but not all the same field names.

       

      • When importing a PDF using PDF manipulator, it saves a record for each field found on the PDF itself. Each record contains the field name, and its value.

       

      • I have another table that I'm using called "FORMS", which contains all the possible fields on the 4 PDF forms.

       

       

      NEED:

       

      • After importing the PDF I want to populate the matching field names in "FORMS" with the values from teh PDF fields. ( there is much more complexity to the actual record creation and error trapping for duplicate past PDF imports, but that isn't what is tripping me up. )

       

       

      PAST ATTEMPTS:

       

      • I've done this succesfully setting a bunch of variables, and then set fields, with those variables. ( This works, but becomes much more time consuming and almost impractical for additional forms. )

       

       

      THOUGHTS:

       

      • I was next going to try the CF from Rob Poelking ( found on Brian Dunning ) that allows the creation of dynamic variables, however I can't help but think there is a better way!?

        • 1. Re: Help with setting fields from multiple records
          erolst

          Unless I'm mistaken, you could simply do an Import into the Forms table.

          • 2. Re: Help with setting fields from multiple records
            user22862

            That is definately a way, but would give me several records per PDF.

             

            I should have clarified the FORMS table. My appologies, and thank you very much for the input!

             

             

            ADDITIONAL INFO ON FORMS TABLE

             

            • FORMS table has a large number of fields, any field that we may encounter on any of the 4 PDF's we will be importing.

             

             

            ADDITIONAL INFO ON PDF IMPORT

             

            • Each PDF is imported by calling a script that creates a record in a table FORM FIELDS, for each field found on a PDF
            • Each record contains 2 fields that I wish to extract
              • The field name
              • The field Value
            • The single record in the FORMS table, will be used for each of the 50 - 70 records in the FORM FIELDS table.
            • The FORM FIELDS records are then deleted from the database

             

             

            MY CRAZY REASONING

             

            • This reduces the number of records by 70x's +
            • Allows easier viewing options for me inside the database
            • My lack of experience in filemaker caused me to use the PDF manipulator inside my database, because I wasn't succesful writing my own scripts to work correctly with thier plugin. So I simply call to thier ( Productive Computing ) throughout my own.

             

             

            Thank you for any all all input. I am passionate about what I am learning and building here!

            • 3. Re: Help with setting fields from multiple records
              erolst

              OK, I thought you wanted to keep the field records. Normally people look for ways to turn field values into records, not vice versa …

              Kevon Binder wrote:

               

              • This reduces the number of records by 70x's +
              • Allows easier viewing options for me inside the database

               

              But it increases the complexity in dealing with the different fields … and using filtered relationships / portals allows you to arrange related records in almost any order or positioning.

               

              Anyway, you could loop through the records in Form Fields, setting two variable lists; one with the field names, the other one with the values; the go to Forms, create a new record and use

               

              Set Field by Name [ GetValue ( $fieldNames ; $iterator ) ; GetValue ( $fieldValues ; $iterator ) ]

               

              This allows you to do away with having to create variables dynamically, but you need to 'escape' values that have CRs themselves, or your lists will be incorrect. I use three quick'n'dirty CFs that help me to preserve CRs while collecting and processing values:

               

              Delim = "§§§" // just the string

               

              ValueToArrayLine = Substitute ( theValue ; ¶ ; Delim )

              ArrayLineToValue = Substitute ( theValue ; Delim ; ¶ )

               

              So assuming that the field names from the Form Fields records match the field names in the Forms table …

               

              Go To Layout [ FormFields ( FormFields ) ]

              # find the correct records, then …

              Loop

                Set Variable [ $fieldNames ; List ( $fieldNames ; FormField::fieldName ) ]

                Set Variable [ $fieldValues ; List ( $fieldValues ; ValueToArrayLine ( FormField::fieldValue ) ) ]

                Go To Record/Request [ next ; exit after last ]

              End Loop

              Go To Layout [ Forms ( Forms ) ]

              Set Variable [ $TOname ; Get ( LayoutTableName ) & "::" ]

              New Record/Request

              Loop

                Exit Loop If [ Let $i = $i + 1 ; $i > ValueCount ( $fieldNames ) ]

                Set Field by Name [ $TOname & GetValue ( $fieldNames ; $i ) ; ArrayLineToValue ( GetValue ( $fieldValues ; $i ) ) ]

              End Loop

               

              Until the day that FMI will deign to give us real arrays, in addition to our beloved lists …

              • 4. Re: Help with setting fields from multiple records
                user22862

                Thank you VERY much for your time!

                 

                And the three functions!!

                 

                 

                The list function is something that I have not yet learned to utilize well, and I see I need to dive in!