8 Replies Latest reply on Feb 25, 2017 10:28 AM by fmpdude

    How can I combine data from different tables in a CSV export

    hnet999

      I have two tables and I would like to export a CSV file that would include concatenated data from both files.

      I hope the below image to illustrate what I am trying to do.

       

      I haven't used FileMaker in over 10 years when scripting wasn't available ... so any help the fine folks on this forum could give would be appreciated.  I am willing to compensate for a little bit of targeted education to help me figure this out.

       

      Thanks

      Ed

      Combining Fields.jpg

        • 1. Re: How can I combine data from different tables in a CSV export
          beverly

          If you can create calculated HTML, Excel can open .htm as well as .csv

           

          Sent from miPhone

          • 2. Re: How can I combine data from different tables in a CSV export
            fmpdude

            Would you also please post an ERD, or at least the Relationship diagram, to show the cardinality between these two tables (how they're related)?  The descriptions in the two tables don't look like they go together (Keyrings and bears).

            • 3. Re: How can I combine data from different tables in a CSV export
              hnet999

              fmpdude,     There is no direct relation.  Two separate tables. 

               

              I need a script that requests/prompts the user to input a DESIGN_CODE and PROD_CODE. 

               

              The Script will FIND all the matching records in the DESIGN table ... but as they are exported I want the script to pull the PRODUCT_DESC field from the PRODUCT table (based on PROD_CODE input) and merge it with the DESIGN_DESC field for the DESIGN records be exported.      Basically just pull that field one field from an otherwise unrelated database based on the user input.


              I hope that is clear ... it is in my head ... but hard to put into text as I am not familiar with all the proper FMP terms or acronyms.

               

              • 4. Re: How can I combine data from different tables in a CSV export
                fmpdude

                Gotcha.

                 

                OK, how I do stuff like this is to "pseduo-code" it first...outside any programming or script environment.

                 

                So, say on the back of an envelope or any piece of paper, I might do something like you have, but add a little more detail keeping the pseudo-code someone generic:

                ------------

                High-level

                ------------

                1. Get input from user through fields on form

                2. Do a Find for matching records using input search terms in each of the two tables

                     In each table's find results, copy the fields I want to a "CSV-Export" (for example) table.

                3. Export the CSV-Export table to CSV.

                 

                If you can include:

                1. The CSV for each of the two tables (actual data)

                2. The input search terms you want (values for search terms)

                3. The desired output screen or report

                 

                I will show you how you can do this in FMP and I'll flesh out the pseudo code above.

                 

                The reason the up front design is so important is that once you start "coding" or working, perspective can get lost (the forest for the trees). Like photography: once you start managing depth of field, flash strength, F-stops, etc., creativity can get lost (why cell phone cameras are often better than expensive DSLRs).

                • 5. Re: How can I combine data from different tables in a CSV export
                  hnet999

                  that seems exactly what I need.

                  My actual tables are huge, that is why I broke it down to just the simple table I sent above so I could just get the concept.

                   

                  The image in my original post include the input search terms (KRING for PRODUCT & FLWR for DESIGN) and sample CSV output (with only the PROD_DESC and DESIGN_CODE fields being concatenated in the final CSV output.

                   

                  I appreciate your help and your patience as I am not great at articulating in text what is in my head.   But it sounds as if you have figured out what i am looking for.


                  Database referenced above file found here:

                  http://cipx.us/i/MERGE%20CSV%20EXAMPLE.fmp12

                  • 6. Re: How can I combine data from different tables in a CSV export
                    fmpdude

                    Got your FMP file. Thanks. Actually, the level of design material you presented is beyond what most folks do. Good job there!

                     

                    --------


                    But, without knowing more about your business and its business rules, this really sounds like a database design problem, not an FMP problem so much, to me.

                     

                    I took your work-in-progress FMP file and did this:

                     

                    • Created search layout with two global fields in new table INPUT_TERMS

                     

                    • Added auto-incrementing ID fields to DESIGN and PRODUCT tables. Using FMP's "Records->Replace Field Contents", I added the initial serial numbers for these two fields.

                     

                    • Added a new table PRODUCT_DESIGN to hold IDs from the DESIGN and PRODUCT tables:

                    It seems to me (again, not knowing your business internals) that a Product could have "MANY" designs and a design could be in MANY products. So, initially, anyway, this DB design makes sense.

                     

                    Now, at this point, I would need to add an automatic mechanism on data entry (drop down, etc.) to populate the foreign keys. Or, using the data you have, you could now use the "ID" numeric fields I added as foreign keys in the M:M resolver table.

                     

                    Then, you can easily use portals (to view) and regular FMP Finds to Find the data you want (SQL too).

                     

                    Give this idea a try and see if it does the job for you.

                     

                    Let us know how it goes!

                    • 7. Re: How can I combine data from different tables in a CSV export
                      hnet999

                      Thank you for your help, I will play around with that.

                       

                      We print designs to different products and sell them online.   I am using FMP to keep all the information about the individual items we print to and the many designs we can print to those items.   from these separate PRODUCT and DESIGN databases I am trying to create a merged file to upload to the webstores.

                       

                      I don't need any of the export information stored .. just exported like a mailmerge.

                      the DESIGN_CODE will match many records the PROD_CODE will match one.

                       

                      For the example in the database I shared in my last post:

                      1) the user is prompted for DESIGN_CODE and PROD_CODE

                      2) FM finds ALL the matching DESIGN_CODE records

                      3) for each DESIGN record being exported - an export field will be created that combines the text from the current PROD_DESC record from PRODUCTS and the text from the SINGLE record found based on the input in the DESIGN table.

                       

                       

                      in basic old fashion programming logic: (obviously in just logic/concept form as I have not worked with FMP scripting and don't know the syntax yet)

                       

                      get user input DESIGN_CODE from tableDESIGN     (will be many records)

                      get user input PROD_CODE from tablePRODUCTS   (will be one record)

                      var NUM_OF_DESIGNRECORDS = number total PROD_CODE records matching

                       

                      while NUM_OF_DESIGNRECORDS > 0

                            do export

                                export PRODUCT:PROD_CODE as field PROD_CODE

                                export DESIGN:DESIGN_CODE as field DESIGN_CODE

                                export DESIGN:DESIGN_SUBCODE as field DESIGN_SUBCODE

                                export calculation of PRODUCT:PROD_DESC + " - " + DESIGN:DESIGN:DESC  as field COMB_DESC (simply adding both product and design descriptions with a dash inbetween)

                                export PRODUCT:PRICE as PRICE

                                NUM_OF_DESIGNRECORDS = NUM_OF_DESIGNRECORDS - 1

                        end

                       

                      LOL   I am sure the logic and loop above reveal that I am old school as I was a computer science major when FileMaker was first released in 1985

                       

                      I am including an updated version of my first example to include:
                      in yellow: the records found based on user input of KRING for PRODUCT and FLWR for DESIGN

                      in pink are the fields needed from the PRODUCT records found

                      in red are the fields needed from multiple DESIGN records found

                       

                      have I mentioned HOW MUCH i appreciate the help?

                      • 8. Re: How can I combine data from different tables in a CSV export
                        fmpdude

                        You're most welcome.

                         

                        BTW, there's no requirement that in the M:M join table, you would have 1:M both directions. But, the structure is there just in case since you have two keys.

                         

                        You're right, if you aren't taking advantage of relational concepts, you're back to writing while loops. In that case, be advised looping is extremely SLOW in FMP scripts. I would avoid loops in scripts (except for the fewest iterations) if at all possible. A loop that can complete in a little over a second in, say Java or C, can take FMP an hour and 20 minutes in my testing.

                         

                        In any case, if you can do this relationally in FMP, that would probably be best in the long run.

                         

                        ----

                         

                        Relationally or non-relationally, you can still get what you want using values entered by the user in the form with the global fields.

                         

                        In your script...basic idea:

                        From the button that says "Create CSV" on the layout with the global buttons, you call a script that will:

                         

                        0. Create a variable for one of the search terms using the "$" syntax and the SET VARIABLE script step. So, $myVariable, for example, is a variable which has scope and lifetime to the current script only ($$ variables will live beyond the script).

                         

                        1. Switch to one of the layouts

                         

                        2. Enter Find Mode

                         

                        3. SET FIELD (the value will be the $VariableName you got in step zero above)

                         

                        4. Perform Find.

                         

                        At this point, you'll have matching records for that Find. So you can now loop over the "FoundSet" as FMP calls it (the results from your Find) and copy the variables to another layout where you can later export them.

                         

                        Then, you could repeat the same basic idea with the other search term. Of course, you could have created two variables initially above so you would have them both right away. Doesn't really matter as long as you're in the same script and they have different "$" names.

                         

                        When you're done, you could have cleanup code to run to make sure you don't have any blank fields or garbage or whatever. Then, do your export to CSV.

                         

                        I think once you start coding it, it will be relatively simple.