12 Replies Latest reply on May 22, 2012 3:59 PM by philmodjunk

    Calculation - prepend text to first iteration?

    JamesGrubic

      Title

      Calculation - prepend text to first iteration?

      Post

      I have a calculation with that creates a very custom formatted CSV. It's a bit of a kludge in that one calculation field combines all of the fields I need in a specific order, and inserts commas to match empty entries where I don't have equivalent fields in my database.

      When I export, I just export this one field and it's already properly formatted with all of the data in the right order, etc.

      It's working well, except I need to prepend some custom information to the first iteration only. That would be fine for me to just copy/paste into the header file, but I have about 20 other users wanting to do this and I'd like it all to be combined.

      So let's say my export looks like this (2 records export):

      "John","Doe","1313 Mockingbird Lane","Tuscaloosa","Alabama"

      "Jane","Doe","1 Infinite Loop","Cupertino","California"

       

      I need the very first line exported to contain some extra info, or a "header". 

      Like this:

      #
      # Header: keys to import
      #
      First-name,Last-name,Address,City,State
      # Records: values to import
      #
      "John","Doe","1313 Mockingbird Lane","Tuscaloosa","Alabama"
       

      Is it possible to have adjust my calculation so that the very first iteration only contains those few extra lines of text?

       

      (I know you may be thinking to just use a "Merge" type of export but the headers won't match at all, which is why I need to kludge the header as well as the formatting order of the data.)

       

       

      Thanks for any advice

        • 1. Re: Calculation - prepend text to first iteration?
          Sorbsbuster

          Ignoring the header question for the moment, I'm curious why you didn't just export it as a merge file (or other format), with the fields arranged in the export sequence you wanted?

          • 2. Re: Calculation - prepend text to first iteration?
            philmodjunk

            You could export from a different calculation field with:

            List ( "#" ; "# Header: keys to import" & "#" ; "First-name,Last-name,Address,City,State" ; "# Records: values to import" ; "#" ; Yourexisting FieldListingValuesGoesHere )

            • 3. Re: Calculation - prepend text to first iteration?
              Sorbsbuster

              Phil - does that not suggest that James will be building the data from many records into one big field?  At first I thought he was looping down the records building up one huge variable, but then he said he was giving an example of 2 exported records.

              I think I'm confused.

              • 4. Re: Calculation - prepend text to first iteration?
                philmodjunk

                All we know is that the data, as exported produces multiple records in the system that imports this data. We have no clue in this thread exactly how that data is assembled--could be from any number of different records from any number of tables or just fields from a single record.

                That's why I suggested something that leaves the existing field unmodified.

                • 5. Re: Calculation - prepend text to first iteration?
                  JamesGrubic

                  The reason I can't use the merge is because as I said the export has to compensate for many fields that are NOT in my database. The header file defines these, and it's unfortunately immutable.

                  As a result the header/export looks more like this:

                  #
                  # Header: keys to import
                  #
                  First-name,Last-name,Address,City,State,Birthdate,FavoriteColor,DogsName,CatsName
                  # Records: values to import
                  #
                  "John","Doe","1313 Mockingbird Lane","Tuscaloosa","Alabama",,,,

                  The extra commas are basically trapping out the extra data that the template requires, plus the header field names are completely long and bizarre and I can't rename my database fields just to accommodate. So that's why I can't use a Merge.

                   

                  The calc I am doing compiles all of this info for ONE record, and when exported in a found set of 20 records it will have 20 lines.

                   

                  Would just like to have the first line to have the header info.

                  • 6. Re: Calculation - prepend text to first iteration?
                    philmodjunk

                    make it a calculation field with an If or case function that only prepends the additional data when get (RecordNumber) = 1.

                    Or

                    create a dummy record where this field only contains the header data.

                    Hmmm, how are you exporting this data? If you export as CSV, you'll get quotes where you don't want the as Filemaker attempts to keep the commas in your data from being interpreted as field delimitters. If it's one line of text per field, I think a Tab export will work here...

                    • 7. Re: Calculation - prepend text to first iteration?
                      JamesGrubic

                      Phil, I tried what you posted and it appears on every line in the export.

                      • 8. Re: Calculation - prepend text to first iteration?
                        philmodjunk

                        It shouldn't

                        If ( Get (RecordNumber ) = 1 ; "headerInfohere" & ¶ ) & Yourexpression here

                        should only include the header info for the very first record in your found set.

                        List ( If ( Get (RecordNumber ) = 1 ; "headerInfohere" ) ; Yourexpression here )

                        should also work.

                        • 9. Re: Calculation - prepend text to first iteration?
                          JamesGrubic

                          You've got some stray quotes/parentheses etc...but I did workaround it and have it working except for the line breaks are not correct in the resulting csv file. As a result the import is failing. 

                          I can manually hit carriage return in the resulting file and it works but isn't there a way to include an ASCII code in a Filemaker calc that works?

                           

                          I should also add that I am exporting as Tab delimited but renaming to .csv, and creating any needed quotes inside the calculation. Stupid thing works as I said, I just need the header.

                          • 10. Re: Calculation - prepend text to first iteration?
                            philmodjunk

                            ¶ inserts a carriage return. The values returned from List () are also separated by ¶ everytime you see a ;, but any missing items of a list also omit the ¶ following it to keep the list without any empty values.

                            I would think you just need to include a ¶ in the place where you need that line break.

                            • 11. Re: Calculation - prepend text to first iteration?
                              JamesGrubic

                              ¶ does not insert a carriage return in the resulting export file. I have them all over the place within quotes. It looks that way in the field in FileMaker, but it doesn't get exported that way.

                               

                              I've been trying char(10) and char(13) as well with no luck.

                               

                              I've got all of my .csv files set to open in TextWrangler, and the app I am importing into (DeployStudio) won't work without the carriage returns.

                               

                              I will be able to spend a little more time on this tomorrow, thanks for your help.

                              • 12. Re: Calculation - prepend text to first iteration?
                                philmodjunk

                                Oh yeah, the return get's converted into a different value during export and there no simple way with a text export, as far as I know that get's around this.

                                You might look into xml exports with an XSLT "grammar" designed to format the data as needed. I think that will work here.