Can you save a layout as CSV with out having to pick the order of fields?

I am working on setting up custom headers for exports and this is making me switch my export script from using "Save Records as Excel" which very conveniently lets me bypass picking the fields and instead just exports it how it is on the report layout. As far as I can tell if I want to export as CSV I will need to use "Export Records" script step and as far as I can tell this will require I pick the fields and put them in the order I want. I am hoping someone will have some more information that will tell me otherwise.

I think that is enough information but just in case here is some more background.

I have many reports for my customers and it seems I am adding new ones all the time. I find that my customers like if they can view the report before exporting it and so I have made layouts and then give them the option to "Save Records as Excel" without dialog or with so they can if they want, customize the report. I would like to continue this same option but switch it to CSV so that I can swap out the headers with there on headers.

So in short can I export a layout as a CSV without needing to assigning fields or order of the fields?

I'm not aware of a simple way to export any layout/record set to CSV. You will probably have to build the functional yourself.

For right now I am just using the export records it is just a lot more work. I will make a post over at Clairs to highlight one of the main issues with it. If you have long filed names you can't see the whole field name when picking from the list. Maybe now that they are making faster upgrades they can fit that fix in some were.

Thanks for the response though. It is helpful to know I am not just missing something simple.

Hi, it sounds like you want to allow users to choose fields and headers when they export reports. If I did not misunderstand your question, maybe you could check blog post in FileMaker Hacks and their demo files?

1 Like

I did consider using XML but instead went with open data file using CSV. That process is very easy and is working but I need to pick out the fields instead of using a saved layout. The link you posted though is far more detailed then the one I found using XML, I will look into it more.

Thank you.

You know how to get the list of fields that appear on a layout don't you? Also, you can provide the fields with layout object names which could be used as your header column data.

I know how to do it when not in script step. Is there a way to do it while setting up a script? Also if I recall it dose give you the filed on the layout but not the order. It is still helpful but not as good as save as excel.

If you don't mind using a plugin – such as MBS – I might have a solution for you:

  • In my demo file I gather all the fields on the layout by using the function
    FieldNames ( Get(FileName); Get(LayoutName ) )

  • Then I replace the Linefeeds with commas and use it in an SQL Statement.

  • After that I export the result with the MBS Plugin as a CSV File.

If you don't want to use a Plugin you might also use a virtual list technique (not quite sure if that works since I haven't tried that).

See my demo:
Export_visible_as_CSV.fmp12 (692 KB)

2 Likes

Another option is to compile the data you want to export into a global field, and then use the BaseElements plugin ( or your plugin of choice ), and write the data to a csv directly.

If, by chance, you were sending that file to Dropbox, you can write the file directly to the Dropbox API. As we found out by accident.

@cheesus and @jormond

My curiosity is piqued as to why your suggestions are to use a plug-in to do the file-writing, without mention of the file writing script steps introduced in v.18.

Is there something about the file-writing steps that is not up to the task, or any other consideration that I should be aware of? Thanks. Just curious.

The order of the fields is the creation and tab order overrides that.

1 Like

I suppose one reason could be using runtimes. They didn't get new funtionality since v14...

Another one could be having implemented those in versions prior to v18 and just no need for codechange.

At least these are my reasons

1 Like

The file writing scripts are fine, as long as you work around the 64 MB limit.

1 Like

Since I gather all the Data in a variable I'm not aware of any script step, that can write it directly to a csv file. The only native CSV method I know is the "export" script step, that contains the mentioned downsides.

As @steve_ssh mentioned, and depending on the size of the data, you can use the Data file script steps to create a file, insert the data, and close the file. It's more developer intensive, but you get full control.

Oh this is very interesting I will be checking that out.

OK, I updated my knowledge now: I wasn't aware of these new script steps, because as @harvest already pointed out correctly, I am still building Runtimes and use mostly FMP 17.

2 Likes