I'm working on a database for veterinary exams. During an exam, a set of measurements on an animal can be taken but not all possible measurements are recorded during each exam. Therefore, my current design has a parent table called 'Exams' and a child called 'Measurements' and they are related through the primary key of the 'Exams' table so that each measurement that is taken is recorded as a new record in the 'Measurements' table (with a label, e.g. 'right leg length', and a value, e.g. '50.5cm'). See attached screenshot of relationship graph.

During the actual exam, data are recorded on a paper form (please see attached photo for example of form). To streamline the data entry process, I would like to recreate this form in digital format within an 'Exams' layout, where all the possible measurements are shown within the digital form (just like the paper form) but where only the measurements that are filled in have a record created for them in the 'Measurements' table. This is where I'm a bit stuck, I'm not sure how to go about this as using a normal portal to the measurements table will not allow me to recreate the form with the same design as the paper form. 

I've got a few ideas as to how to make this work, but they all seem a bit clunky and was hoping someone here had a better suggestion as to how to go about this? I think the easiest would be to create fields within the 'Exams' table for each possible measurement and removed the 'Measurements' table all together, but that will result in having a lot of empty fields and will make reporting more difficult (correct?), and there are a couple other procedures/table (e.g. 'Necropsy') where measurements are taken as well so I'd like to have a single table with all measurements in a single location. I could also create a helper table where the measurements are entered, and then a script creates the necessary records in the actual 'Measurements' table, but this seems it could be highly prone to error/problems and would result in duplicated data.

Please let me know if any clarification is necessary. Any help is much appreciated! Thanks.

IMG_9241.HEIC

Have a look at the attached example.

SurveyAllQuestions.fmp12

Thanks Comment! This is a very nice solution. It doesn't allow me to quite reconstruct the data entry form in the same layout as the paper form, but I think I should be able to get this to work. Appreciate the help!

52 minutes ago, Eli Walker said:

It doesn't allow me to quite reconstruct the data entry form in the same layout as the paper form,

You should be able to get very close (or at least as close as can be practical for an on-screen form). Keep in mind that you can split the questions into multiple portals (with each portal starting at a different row), then position and format each portal individually.

Okay great, that’s what I was thinking so glad I’m on the right track. To create the portals is it best I create a new occurrence of the child table and relate it and the original on an alternating match key to the parent (to show a different question on the same ‘row’ within the viewable layout)?

Really appreciate the advice! 

1 hour ago, Eli Walker said:

is it best I create a new occurrence of the child table

Oh no, not at all. Just duplicate a portal and set the duplicate's initial row to say 11 (assuming you want the first portal to show 10 rows). Then duplicate it again and set the initial row to 21 - that's how you would create 3 portals,  each showing a group of 10 consecutive rows.

I realised that I had misunderstood what you said right as I sent the message.

Great, makes sense for sure. Thanks again!

On 12/31/2018 at 5:02 PM, comment said:

Oh no, not at all. Just duplicate a portal and set the duplicate's initial row to say 11 (assuming you want the first portal to show 10 rows). Then duplicate it again and set the initial row to 21 - that's how you would create 3 portals,  each showing a group of 10 consecutive rows.

Hi Comment, just another follow up question here. I've implemented the design you said without issue and it works perfectly, thanks so much. How would you suggest setting things up so that you could easily delete a value that was entered? I was just going to put a button on the portal that triggers a script to go and delete that specific answer record, but thought perhaps you'd have a better way of doing this.

Thanks again for the help!

 

If it is important to not have empty records in the values table, then you should attack this on two fronts:

• In terms of data integrity, you should validate the field as not empty so that the user will be forced to either enter a value or delete the record;
• In terms of user interface, you can make the user experience smoother by either showing a custom dialog presenting the choices, or just have a script trigger automatically delete the underlying record upon detecting that the field is empty - without requiring any action from the user.

Ah, okay. I understand and that was what I was looking for. Thanks! From your first comment are you suggesting that it's not necessarily a problem to have empty records within the value table?

It can be a problem if you're counting the records for some purpose - e.g. how many answers on a quiz. I am also not a fan of redundant records, so I'd rather not create a record unless it has some data. But once it has been created, it's a question of how much effort is it to get rid of it versus what is the harm in keeping it.  I have seen many developers pre-create a full set of records in these situations without giving it a second thought. 

Okay, thanks for the insight. Really appreciate your help with all these questions Comment!