Save as Excel - column order

[XsTatiC]

First off... I'm pretty much a noob at FileMaker. I have actually tried searching for this answer, however my search queries are giving me anything but what I'm looking for.

I have a layout setup. It contains 3 fields and then a portal with another 10 fields. The only reason for this layout is to export to Excel the specific fields which need to be exported. This is done 100's of times a day in our office.

Right now I have everything exporting. Data is fine. All I'm doing is hitting Save as Excel. Because the rest of the people in the office aren't computer savvy, I would like to make this as simple-stupid as possible for them as well and just have them hit "Save as Excel" from the top toolbar.

The issue is with the column order. It's all over the place. It doesn't match the layout in the portal. So instead of Address, Address1, City, ZIP/Postal Code, Country... etc (fields as ordered in portal), when it's exported to Excel it comes through as Zip/Postal Code, City, Address1, Thisisamess, Howaggravating, Address.. etc.

What am I missing? I'm 99.9% sure the export can come through ordered as expected and that it's me and my lack of knowledge that is missing a step here.

Help, pointed in the right direction, cold beer... anything would be appreciated at this point.

Thank you for your time.

Cheers.

[bruceR]

Not an answer to the question but if you're exporting hundreds of Excel files - why? This sounds ripe for a much more productive solution.

[XsTatiC]

There's a lengthy explanation... but that's not important. It makes sense for the nature of the business.

[fseipel]

I wasn't clear on whether you were using a script to export, or the Save as Excel command, but it sounds like you're using 'Save as Excel'. Instead of using this, I'd suggest using 'Export Records'. This will allow you to set the field order independent from the layout display, including more or fewer fields in whatever order you please. This will require creating a script, and a pushbutton to trigger that script. I'm not sure what field order FM uses when you 'save as Excel'. The users will still need to push only one button. Another advantage to the script method is that you can assign a meaningful filename automatically.

Many years ago when I worked with Macs, I brought data from/to Excel with AppleScript. That application was Filemaker integration with a laboratory information management system (HP ChemStations). A simple pushbutton on the Excel sheet, would interchange data, so the user, never had to open FM. This may be worth mentioning, since it would avoid the need to switch between applications; just filling in the fields in Excel would bring in the data into a new Excel tab/worksheet or refresh it when you click the button to run the VBA/AppleScript. In fact it worked better on Mac than PC; when the company later switched to Windows, getting data back into FM was more complicated (had to have VBA write out a CSV, and tell FM to run a script) vs AppleScript which let me to go the table/field and set the value directly. Alternately, you may wish to consider ODBC, which would allow Excel to directly import the data.

[XsTatiC]

Thank you. I'll create the script tomorrow. I was just using the Save as Excel button in the nav bar... I had just figured it's export behaviour could be manipulated dependent on the layout.

As for AppleScript and your other solutions... I wish my case was as simple and clear cut that I could actually implement such a logical solution. This Export Records script should fit the bill though.

Thanks again.

[XsTatiC]

The Export Records worked like a charm.

I do have another question though. Not sure if I should open a new topic for it.

Scenario:

When users are browsing a lot of records, stop to view the record they want to export at the moment they have to click into any field before they hit the button that will take them to the new layout where the export is to ensure that it's actually that record.

How to I make it automatic from the record currently being viewed?

My script is most likely inefficient... and wonky. That said, here it is:

New Window [Name: "Address List"; Top: 30; Left: 30]

Go To Layout["T: Address List"(T)]

Enter Find Mode []

Insert from Last Visited[select[T::code]

Perform Find[]

Then it saves to Excel.

The Insert from Last Visited seems to be the error. Visited vs Viewed.

Help would again be much appreciated.

Thank you for your time.

Cheers.

[Fitch]

Set Variable[ $code ; T::code ]

New Window [Name: "Address List"; Top: 30; Left: 30]

Go To Layout["T: Address List"(T)]

Enter Find Mode []

Set Field[ T::code ; $code ]

Perform Find[]

[XsTatiC]

Works like a charm. Thank you. It looks very obvious once I see the correct way of doing it.

Thanks again.