9 Replies Latest reply on Jul 13, 2011 1:12 PM by aammondd

    Dynamic Creation of Files - Decision Critical

    TimRiley

      Title

      Dynamic Creation of Files - Decision Critical

      Post

      Hi All,

      I have a very tricky one for you...

      I have a Customer Table( one field -cust_id), a Store table(two fields -cist_id and store_id)

      and an Item Table(two fields -Item_id and OrderQty)

      For each Customer, they may have one or many Stores and for each Store the need to be able to order one or Many Items.

      Here is the problem.  Lets say they have Three stores and they order a few items for each store.

            I need to output one file for each store with one record for each item ordered.

           the file name should be CustNum-StoreNum and the fields item_id and qty

      OR

          One file with four fields-  cust_id, store_id, item_id and qty

      which ever one is easyer to do.

      I know I am asking allot but just a Yes it can be done or a No you cant is all I really need.

      any hints beyond that would be greatly appreciated... Thanks

        • 1. Re: Dynamic Creation of Files - Decision Critical
          philmodjunk

          Yes it can be done, depending on the format of the file you want to export. Text? Excel? PDF? Filemaker? or ...?

          • 2. Re: Dynamic Creation of Files - Decision Critical
            aammondd

            Either should be able to be done.

            One way in which ive seen similar done was for records to be built in a temp export table and  then use variables to name the export file exporting only the records in  the temp file. You could do this repeatedly through a series of loops.

             

            • 3. Re: Dynamic Creation of Files - Decision Critical
              TimRiley

              excelent!  Just about any file type is fine as I will be writing the import application that reads them also :-)

              Thanks guys

              One more question...

              I am imagining how I will need to be able to add records to the dynamicly created files

              ... will I be able to do this kind of loop and file control in filemaker pro or will I need

              filemaker advanced?

              • 4. Re: Dynamic Creation of Files - Decision Critical
                philmodjunk

                You should add the records before you export. If you export records 1-5 then need to add records 6-8 to it, re-export with records 1-8. I don't think you'll need to use a temporary table just to export this data unless it is required in order to radically alter the way the data is structured (Such as changing rows of data into columns). You can just find the needed records and then export that found set.

                I suggest you experiment with the export records tool to see how it works and the many options you have when using this tool.

                • 5. Re: Dynamic Creation of Files - Decision Critical
                  TimRiley

                  can i do this in filemaker pro or will I need filemaker advanced?

                  I will need to create a dynamick number of temporary tables, keep track of them, append recordes into and update them all on the fly.   I am thinking now that perhaps I would be better off with Xcode.

                  • 6. Re: Dynamic Creation of Files - Decision Critical
                    aammondd

                    Not sure why you would need a dynamic number of temp tables you only need a variable filename that can be derived from data.

                     

                    For example you are processing daily vendor orders you would find and sort the records you want use a loop for each vendor add the records for that vendor to the temp table when the vendor changes export the file before clearing it out for the next vendor. When you hit the end export the final file. the export will take a variable as the file name this isnt too difficult. If you need different fields exported you may need a different temp table but if you are exporting records as say a csv file you could even construct the CSV string into a single field and export that. This would allow you to use this same temp table to produce multiple different CSV formatted files.

                    Im not sure why you would need to export a partial list of records to a file and then append more at a later date you could just rebuild the file at any point in time if its as you say customer based. You could then overlay the file.

                     

                     

                    • 7. Re: Dynamic Creation of Files - Decision Critical
                      TimRiley

                      you are correct... it could be just one file with the Cust_id, store_id, Item_id, order_Qty... that would work fine.

                      Can I do this in Filemaker Pro?

                      I do not see anything in help about creating a temporary table that I can append records into?

                      where should I look for this information please?

                      • 8. Re: Dynamic Creation of Files - Decision Critical
                        philmodjunk

                        I always recommend Getting FileMaker advanced for development work, that's what it's designed for after all and its tools will save you hours of frustration. Set up your clients with FileMaker Pro as there is no difference between the two applications when you are just using a database designed by someone else.

                        We export data to a fileMaker archives file in one of our systems every night.

                        Script 1 is defined in the data entry file to perform a find to pull up all the records in a found set. Its next step is to use Perform Script to start up a script in the Archives file. That script uses Import Records to import the data from the data entry file's found set to add those records to those already present in the data entry file.

                        That sounds like a process that may work for you here.

                        • 9. Re: Dynamic Creation of Files - Decision Critical
                          aammondd

                          we call it a temporary table only because records stored in it are temorary in nature (ie they exist there only long enough to export them) you would need to create a real table for this purpose.

                          As Phil suggests you could also do this without by doing multiple found sets too.

                          All this is possible with FM ive seen similar done many times.

                          the scripting language to do  this isnt very difficult the most tricky is doing the finds and the loops.

                          As Phil suggested play around with the export records dialog. You can use a script step to export records with preset dialog settings.