We are migrating our database from FMP to some RoR environment (pfff...) and I need to tick off the contents of 30 Fields against each record.


  1. Is company name correct?
  2. Is Number of products identical to the list in the legacy DB?,
  3. Is Product Category correct and so on)

What is the best / most efficient way to do this in FMP: Do I create a new Table & Layout with separate Fields for each Question/TickBox, or design a new Layout based on an existing Table that has most of the Fields we need to compare and create just one new Field for the TickBox?

Let's bet that the request will change in some way. In that case, having the questions appear as line items in a portal for each Questionnaire is the most flexible. Answers to questions are normally separated too.

So you end up with four tables: Questions, Questionnaire, Questionnaire_join_Questions, Answers.

Give Questions a field for ordering/sorting so that you can control that in the portal and reports.
Depending on what a Questionnaire represents, you may want a field for ordering/sorting that too.

Over in the Answers table you summarise by Questionnaire, sorting by Questionnaire order and by Question order.

1 Like

For questions that are negative, do you have to correct it? If so, if you don’t know the correct value do you want to be able to report those?

From a user standpoint I would proceed differently..

I would create a layout where you have side by side the legacy and new db record the latter modifiable

There would be 5 columns:
RecordID, Note (for auditor, what is expected if not obvious), legacy value, new db value, verification button (logs auditor confirmation).
It minimizes cognitive disruption and auditor can click it’s way down

That said I would prefer clicking on the non conform values which would become greyed and reportable. This simplifies reporting and on leave record, dialogue request auditor confirmation then all that are not greyed get approved mark.

1 Like