Best practice for uploading multiple records to FM file

We would like end users to be able to upload sets of records to the business file through betterforms. Is anyone else doing this as a business practice? I am interested in best practices, as well as ways to speed up the process and make things easier for our users in the process.
We are looking to:
1 - have user download an excel file with the template and fill it out with their records
2 - have the user drop the file into bf
3 - have BF pass the file to the business file and create new records in the business file from the excel data…


How many records would the user be entering over what time period? In my opinion this will determine which is the best approach. We recently completed a BF project to enable teachers to enter student reports. By carefully designing the UX we were able to enable one member of staff to enter 160 reports at a rate on 1 every 16 seconds!

I guess I’m suggesting that a native BF method may provide the best user experience in more scenarios than you might expect - it was certainly very simple for our users.

If you are interested in how we did this, do let me know and we can arrange for a voice or video call.

Obviously, if you envisage the user doing a load of copy and paste from other data sources, a spread sheet might be the better option.

1 Like

So here is the app.

The teacher logs in and is automatically set as the Reporter (those with permission can add reports on behalf of others)

The modal is opened by the “Create New Report” button and need never be closed until all reports have been entered.

Once the date has been entered, clicking the “Next” button runs a hook that creates the report record and updates the list on the background page. It also clears out the student and the grades, but not the subject, to minimise the effort by the teacher. Although there are several hundred students this is not a problem as the select list is searchable and the list also remembers how far down the list you got when you click “Next”

I think this kind of approach would be useful for many data collection scenarios

We also enabled Staff to edit their own entries after creation using the same modal and “Save” does just that and closes the modal - useful if you spot a fat finger error!

Thanks for this Paul, looks great.

To add to this a bit, We have built apps where there are random excel files of various widths and column types that needed to get uploaded. In our case, a template was not an option.

We used a staging table and gave each input a session id. The staging table simply imported the excel file and had more fields for columns than any of the import widths would be.

We then presented the user with the data and let them select what column was what with dropdown and then completed the import.

For record creation you can do anything you want as it’s FM. I personally use a selector to create records via a single system script. We never touch records directly but this is a lot more advanced but also gives the benefit of a single transaction for creation.

As will all things BF, it user comes first so if it’s going to be a slow process, then spin it off to a PSOS routine.


this is a great solution (and for someone building something for schools, I really appreciate the approach). My users will be loading sets of records between 150 and 1200 rows long (basic address information, non-identifiable). I think the idea of a staging table is probably a good one, to also show the user the number of files uploaded, etc. Paul, I’d love to touch base with you on what you’ve built…thanks!