AnsweredAssumed Answered

Store Visit database planning advice

Question asked by AjEGfmTech on Oct 21, 2014


Store Visit database planning advice


I have posted a similar question before about 'Forms'. I continue to struggle with some of the concepts about how this needs to work, so I'm working on a simpler prototype to make sure I have the concepts down first.


I'm looking to create a database that our staff can take on their iPads to visit a store and fill out a form evaluating the store. The form has many items to be answered. There can be many versions of the form depending on which items are selected when the form version is created. Each visit can only use one version of the form. Once a visit has been started, that version of the form has to be locked to that visit and cannot be altered.

Presumed Steps

I figured I would need to accomplish the following.

  • Create and join the main tables: Items and Forms.
  • Allow for Selecting Items for each Form version.
  • Allow for Selecting a Form version for each Visit.
  • Copy/Import the Form version Item records into the Item TO linked to the Visit record.
  • Filling out the Item records linked to the Visit record.

My main questions have to do with the last three items above.

My prototype starts out with three TOs.

Items ---> Join_ItemsForms <--- Forms

Select Items for Form Version

For each new 'form version' I need to choose from the full list of Items, so I setup the following TOG.

  • FormsSelect (new TO of Forms) ---> FormsSelect_JOIN_ItemsForms (new TO of Join_ItemsForms)
  • FormsSelect ---x FormsSelect_ITEMS (this is a cartesian relationship)

I use the cartesian relationship to show ALL of the Items in a portal. I use a technique that PhilModJunk showed in an old (2011) 'Many-to-Many' demo database that uses a 'AddRemove selected Items' script. (Is there any way to select all the text in a script and copy?)

The script was basically...

Freeze window.
Set Variables for the primary keys in Forms and Items.
ValueCount  (FilterValues (List (Join table::Items foreign key); ItemsSelect::primary key))
Goto Layout and enter Find mode...
SetFields (Join table::Forms foreign key; variable) for Forms and Items
Perform Find…
Delete record
Goto Layout and create New record...
SetFields (Join table::Forms foreign key; variable) for Forms and Items
End If
Goto original layout

I put an object in the portal row with this script attached which, when clicked, would toggle between adding and removing join table records that would link the clicked Items records to the active Forms record.

Select Form version for Visit

This is where my understanding of the concepts starts to break down. My presumption is the following new TO's.

Visits <--- visits_FORMS

This allows me to link a visit to a form, but only if I know the primary key of the form. What I'm trying to accomplish is to allow the user to select the version of the form to use for the visit based on the complete list of forms. To accomplish this, I thought it might work to do something similar to the 'Select Items for Form Version'...

Visits ---x visits_forms_VIEWALL (this is a new TO of Forms in a cartesian relationship)

I figured I could use this to setup another 'pick list' portal to allow selecting the form version for the Visit. Unfortunately, this didn't work. It still required me to manually enter the primary key of the form AND the pick list never showed all of the form versions.

Next Steps

Copy/Import the Form version Item records into the Item TO linked to the Visit record.
This would presumably involve creating a new table or a new TO for 'ItemsVisit'. I don't know if that requires a new table or could/should be accomplished in the same table, just with a new TO?

Filling out the Item records linked to the Visit record.
I presume that this will be pretty straightforward - just create a new field in the Items record to 'fill out' the answer.

The whole database will actually be more complicated than this because the Items are part of a Measure (score) which fit into a hierarchy of Sections and Categories, but I need to get the basic concept of Items > Forms > Visits down first.

Thanks for any help you can offer.