I’m currently trying to recreate a specific Excel format that I’ve seen before, but I’m struggling to replicate it accurately. Despite researching and attempting various approaches, I haven't been able to achieve the desired result. I’d greatly appreciate any guidance or tips from those more experienced with Excel formatting.
The format I’m aiming to replicate is shown in the screenshot below. I’m specifically looking to recreate the layout, including the file name and cell names as indicated in the screenshot.
Hi @Saeed , Are you just trying to create a layout in Table view that has "ID", "TRANSACTION_DATE", ETC. as the column headings (field names) as in your screen shot, including red text color for the ID column? Do you need the Excel "A", "B", "C"... column headers?
Probably I'm missing something as I read your request, but it seems that using Table view will get you close. Note of caution: I never give users access to Table view as it can be a bit "dangerous" with adding new, unwanted records and such accidentally. If we need a spreadsheet-look we design a List view to emulate it.
Please provide a bit more info, as it's likely I don't quite get what you need.
As mentioned, I'm not sure I understand your needs, but here's a very quick example of something that looks similar (to me) to your screen shot. If you need the row numbers at left, that can be added using a serial number or {{RecordNumber}} symbol, a calculation, etc.
I prefer List view to Table view for users. I only use Table view for dev, especially for tables like "LocationHistory" and similar, but they're only accessed (if ever) by developers. (During development I do often have Table view of layouts to view a wide range of data at once.)
I need to generate this specific Excel format directly from our FileMaker-based attendance system. Currently, if someone forgets their ID, security records their ID, check-in, and check-out times on paper. This information is then manually entered into an Excel sheet on the PC using the specific format, which is later sent to the relevant team for SAP system upload.
To streamline this process, I developed an attendance system where employees enter their ID, receive an OTP via email, and can then check in or out. However, I’m still facing challenges in exporting the data to match the required Excel format.
At the end of the day, security can search or log into a specific layout to view check-in and check-out records, then generate an Excel sheet. However, this must follow the exact format above; otherwise, the SAP system will not accept it.
Please be informed that everything has been completed except generating the specific Excel sheet format.
To dictate that the Excel file name is as you have it in your first screenshot, set that in the export $path definition to suit your needs. Don't forget to include the file extension.
If your table has field names exactly as you show in the screenshot (and I matched in my example) then the export will have the same column titles as your screenshot.
If your table uses different field names than needed on the Excel output, export/import first to a temporary table (with field names as needed) using the Get (TemporaryPath) function for the export, then import into the utility table from which you export to Excel. This is just to nicely control the Excel heading names.
If your table uses fields which are related to the table from which you are exporting, the column headings will look like: YourTable::TRANSACTION_DATE. This can be ugly and will likely cause you issues, so if this is the case I suggest you export your found set of records to a temporary FileMaker table (a utility table as mentioned above) in which you can define each field name as you need and map them accordingly on the first export test. This will allow you to have whatever column headings you need on the Excel file.
One could take a more complicated approach (exporting as XML format using FMPXMLRESULT grammar) but to me that seems overkill for what you describe.
If you need help with any of this please let me know. I can create a new example for you.
To clarify a bit regarding the process of exporting from your table to a temporary utility table in which we control the field/column names: We must define context each step of the way.
So...
First we define our found set
Export to the Temporary Path (FM hold it for us)
Change context by moving to our temporary table TO (I use a form layout with no fields on it for performance). This table has our field names as we need them.
I do this stuff on a "utility" window off-screen (position left -4000)
Import the records we put in the TemporaryPath location
Export from this utility table to Excel using a path definition with our required file name
Delete the records in the temporary table for use next time, or do it at the beginning of your process (in the script that does this whole export thing)
The end
If you need help with this let me know.
Edit to add: Excel doesn't let us control formatting of data, so date and time formats may not appear as needed, so manual adjust of cell formats may be required. This is where one would consider employing the above mentioned exporting as XML format using FMPXMLRESULT grammar.
Thank you for the suggestion! I agree that exporting as XML might be more complex than I need. I’d appreciate it if you could provide an example using a simpler approach. That would be very helpful—thank you again for your support!"
Here's a v2 of the example file which may be a bit closer to what you need, @Saeed .
Note:
I did not bother to change the ID field to an employee ID, and left it as the record ID. I'm aware that you need that field to be different (lazy me).
In this case, since I already had the original "main" db table fields named, the TempUtilityTable fields are named the same. This could just as easily use other names in the primary table and map them to the output field names in the TempUtilityTable.
IF I UNDERSTAND YOUR NEEDS in that you need the Excel file to have the cell data formatted as you show in your screenshot, such that dates are "30.10.2023" and time is shown as "14:30" w/o seconds, you will need to export the data as text to Excel or Excel will likely make some assumptions and format dates as DD/MM/YYYY and time as HH:MM:SS. Therefore, the "temp utility table" can receive the data as text from your primary database table and we can manipulate it for export to Excel as text. The TempUtilityTable uses Text-type fields rather than Date and Time. See the second script in this file to see if it gets you close to what you need.
One could try to use date and time type fields and forcing it a bit by using Date () and Time () functions, but you may end up with ?.?.? or similar results. FM likes date and time fields to store data in its expected way in the back-end and provide display options in the Inspector. This isn't helpful for such exports, hence the massaging of the data in the temp table (and why we have the option to work with XML as the export as well).
In this case, we export our data to the TemporaryPath; move context to our TempUtiityTable; import the data from the TemporaryPath (stored in Text-type fields; massage the data in our script loop; then export the data to Excel. Excel will see the Date and Time data as text. I don't know if that will cause an issue with your SAP software.
I tried to provide comments in the script(s), but if you have questions please ask.
If I've misunderstood (again) and the Excel cell formatting isn't the issue, you can disregard the substitute parts and field types changes.
Multitasking (badly) here and forgot to mention that to name your Excel file as you have highlighted in the red box in your first screenshot, simply define that name in path (e.g. $exportPath in my example).
Keep in mind that if you cannot include some time element in the file name, exporting multiple times will result in overwriting the previous one unless you send it to its own folder (e.g. include "Create Folders" on).
Anything you share with me is valuable.