6 Replies Latest reply on Jan 7, 2010 7:51 AM by JoeTaureau

    Data manipulation during Export

    JoeTaureau

      Title

      Data manipulation during Export

      Post

      I have no idea if what i wanna do is doable or not, but what i wanna do is...

       

       

      I have 2 fields to export to a Tab Delimited file. Let's call them "English" and "French". If there's more than one entry in the English field (separated by a carriage return IN THE SAME FIELD), and only one in the French field, all those English entries must be equivalent to the French entry.

       

      Exemple :

      English_Main     French_Main English_Sub1 English_Sub2 English_Sub3 English_Sub4

       

      The above exemple must look like this once exported :

      English_Main     French_Main English_Sub1     French_Main English_Sub2     French_Main English_Sub3     French_Main English_Sub4     French_Main

       

      But, here's the problem; sometimes, i can have multiple sub-entries in the French field, but only the FIRST one must be used during the exportation. So basically, this : 

       

       

      English_Main     French_Main English_Sub1     French_SubX English_Sub2     French_SubY English_Sub3     French_SubZ

       

      ... must look like this in the exported file : 

       

       

      English_Main     French_Main English_Sub1     French_Main English_Sub2     French_Main English_Sub3     French_Main

       

      And the other problem is that i must NOT modify the content of the DB itself, that manipulation must be done only while exporting. When the export is done, it must be like it was before the export manipulation.

      So, am i shooting in the dark here or there's a way to do this in FM ?

       

      My first idea was to simply duplicate the DB, do the manipulation and then delete the dup, but that doesn't answer my question about moving/copying the data to make it look like what i need in the exported file.

       


       

       

       


        • 1. Re: Data manipulation during Export
          philmodjunk
             I'd set up some calculation fields that manipulate the text in these fields into the format you need and then you would export from these calculation fields instead of the original text fields.
          • 2. Re: Data manipulation during Export
            comment_1
              

            I suppose it's possible, but not quite easy: you can export as XML and use a XSLT stylesheet during the export to perform the necessary manipulations.

             

            Any other method would require importing the data into another table and breaking it into individual records - because Filemaker will not export in-field carriage returns as such.

             

             

            I am not sure what this is about, but it looks like you should give another thought to your data structure anyway; multiple values in the same field are always problematic.

            • 3. Re: Data manipulation during Export
              JoeTaureau
                

              comment wrote:

              I am not sure what this is about, but it looks like you should give another thought to your data structure anyway; multiple values in the same field are always problematic.


               

              Tell me about it... i have to clean up their freakin' mess :smileymad:

               

              I think i'll just export it "as is" from FM and run a Perl script that do what i wanna do because it looks like i can't really do it directly from FM.

               

              Thanks anyway guys, i appreciate the help.


              • 4. Re: Data manipulation during Export
                JoeTaureau
                  

                Sorry for the bump, but my question is related to this thread, so might as well use it.

                 

                I'm trying to delete the sub-entries in the French field using this calc : 

                 

                 

                Left ( test::French; Position ( test::French; "¶" ; 1 ; 1 ) - 1)

                 

                ... which is working perfectly fine when i have sub-entries in the field like this : 

                 

                ------------ |FrenchMain| |FrenchSub1| |FrenchSub2| |FrenchSub3| ------------

                 

                But when i only have ONE entry in the field, without any sub-entries, the field is emptied when i run the script. Maybe i should use something else ? I tried different Position numbers in the calc, but no luck.

                 


                 

                • 5. Re: Data manipulation during Export
                  comment_1
                    

                  Try:

                   

                  GetValue ( test::French ; 1 )

                  • 6. Re: Data manipulation during Export
                    JoeTaureau
                      

                    comment wrote:

                    Try:

                     

                    GetValue ( test::French ; 1 )


                     

                    Ding ding ding, we have a winner ! :D

                    Now i can do what i wanna do by exporting it to Tab Del. file and by using a simple macro in Excel that will duplicate de previous available data to fill the empty cells below.

                     

                    Thank you sir !