Format issues with FMPA 11 checkbox set exported to Excel
Posted on 2010-08-19
I have addressed this issue at a lower level in 26354304 and on another forum who suggested using VBScript. I have upgraded to FMPA 11 and have Excel 2003, 2007, and 2008 (Mac and PC). The big question is how to export several FM fields composed of multiple checkboxes each to Excel format with semicolon delimiters. FM data is numeric. When it gets to Excel, it appears in one of two ways. Either the numbers concatenate in a cell with box (LF or CR) delimiters, or they concatenate with no delimiters. If the first, when I click on the cell, the box delimiters disappear, and the numbers show up as a vertical column (as I would expect with CR or LF delimiters). There doesn't seem to be a Find and Replace ability in Excel to remedy the situation (I don't know VB). Using csv format on the export or Excel side destroys other unrelated formatting of text fields with decimals that get treated as numbers by Excel.
My logic says I should manipulate on the frontend with FM. One option would be to view the FM list, write a script and/or script trigger to concatenate with semicolon delimiters, save the result as a variable or into another field, and export the new field as a result to Excel.
What would be the easiest approach to script and debug in FMPA 11 (with or without AppleScript), or should I go from the Excel end?