Design advice requested

[Stephen Abshire]

Let’s assume I have a form ‘projects’ and another form ‘consultants’ and I want to assign consultants to work on projects. The relationship is one-to-many projects to consultants which is easy enough. But how can I have a field ‘role’ that will show what role the consultant is performing when assigned to a project. Naturally, I want to be able to assign a consultant to multiple projects as well.

[Brendan]

Hi Stephen,

You can’t assign multiple consultants to different projects when you use a One-to-Many relationship from Projects to Consultants. You’ll need to use the Many-to-Many option instead. That way a consultant can be assigned to different projects.

However, that also means that if you edit a consultant to specify a different role, that role will apply to every project they belong to.

The only way around that is to use a Table field on your Projects form. Then connect the Table field to the Consultants form. Any matching field names in the Table field and the Consultants form will allow you to copy the contents of the selected Consultant record into the Table field on the Project form.

This has the advantage of being able to select the same Consultant record for different projects and assign them different roles. You could add a Role field to your Table field on the Project form.

However, this has the disadvantage of you not being able to select a consultant record and seeing all the projects they’ve been assigned to. For that you need a Link to Form field with the Inverse Relationship option enabled.

[Sam Moffatt]

Instead of taking the Table field, I’d create another form (Role Map for lack of a creative name) which has one field in it initially called “Role” as the text field. From your “Projects” form, create a new 1:M relationship into the “Role Map” form and tick the “show inverse relationship” box. From your “Consultants” form, create a new 1:M relationship into the “Role Map” form and also tick the “show inverse relationship” box. On the “Role Map” form you will end up with a “Role” text field and then two “Link from Form” fields which are set to use a single item.

Then to get this to work, go to your “Projects” form, find the “Role Map” table, click on the second icon (the plus button with an arrow) to add a new record in the subview. This will create a new record with the “Projects” field pre-populated with the project you came from, you can type the role name and then you can click on the ticket for “Consultants” to select the consultant you wish to use. To see the full consultant record, click on the arrow.

Depending on how you want to look at your data, you could then hide a combination of the fields. For example in the “Consultants” form, you could hide the Link to Form field so that when you are looking at a Consultant you only see it’s details not the linking fields. On the “Role Map” field, you could also hide the “Projects” Link from Form field as well. On the “Projects” form, you could hide the “Projects” field from the table view as it’s a little redundant there. Using Form Categories you could also hide the “Role Map” under another category heading and then collapse it on your left nav.

Additionally you can use a “Calculation” field in the “Role Map” form to copy across details from the consultant table. This should get updated automatically when the consultant record is updated though there are a couple of times where I’ve confused TF enough for it not to propagate the changes (the exception not the rule).

I’ve created a document and attached a backup of it demonstrating what that could look like. I originally tried to do a form template export of the “Projects” form but for some reason the “Consultants” form didn’t export properly when I did a clean import. I’ve also got a quick screenshot of what the “Projects” form would look like. It features some of the fields being hidden.

Attachments:

You must be logged in to view attached files.

[Stephen Abshire]

Thanks to each of you and this helps out a lot. Appreciate the download example from Sam and I was attempting to use a link table in my testing, but I missed a few pieces. Would I use the same approach to restrict a consultant to one role per project?

Thanks again!

[Sam Moffatt]

I don’t think there is anything inherent in TapForms that would let you restrict a consultant to one role per project. If I were modelling this in a more traditional relational database, I would set up a primary key on project-consultant fields in the role map to enforce this constraint but TapForms doesn’t have this sort of constraint modelling to my knowledge. Short of creating a new consultant record per project, I don’t think you could model a 1:1 consultant-role mapping.

[Stephen Abshire]

We currently have a ‘roles’ form that links ‘projects’ and ‘consultants’ together. It seems that if we delete a record from the ‘projects’ table the linked associated records in the ‘roles’ table are not deleted which leaves orphaned data. Have you experienced that or was this perhaps anomaly?

[Sam Moffatt]

TapForms doesn’t cascade deletes out of the box if I remember correctly. There’s some nuance in the Link to Form on if it “Deletes” or “Unlinks” for removing child records manually but if you delete a parent record, I don’t think it cascades those changes.

You could use a form script to do a cascading delete function, most of the time I’m adding records and very rarely delete them so it’s not a problem personally I have.

[Philip Jones]

Additionally you can use a “Calculation” field in the “Role Map” form to copy across details from the consultant table. This should get updated automatically when the consultant record is updated though there are a couple of times where I’ve confused TF enough for it not to propagate the changes (the exception not the rule).

Sorry for resurrecting this thread, but I did have a question about the advice above (for my own understanding).

Since the underlying data (the “truth”) is already stored in the related form, why would one copy the data into the calculation field? Is this not redundant as opposed to, say, referring to the original data field using a script?

One possible advantage of doing that (at least that I could identify) would be to transactionally put the related data into a calculation field and then not update the field unless directed to do so by the user. This would have the effect of setting the data at “time zero”, allowing the underlying data (in the related field) to be updated without modifying the related record. In situations where one needs the correct historical record AND the ability for the related forms to have updated information as time goes by, this could be an advantage. However, in my limited knowledge, this is not the way Tap Forms works, correct?

Thanks for any insights.

[Brendan]

You use a Calculation or Script field to copy the value from the parent record into the child record so that Tap Forms can provide the ability to search and sort the child records based on values in the parent record. That’s why Tap Forms copies the value using the Calculation field as a way to do that. You can tell the Calculation field to only evaluate one time. Then Tap Forms won’t put in a new value for it if the Calculation field already has a value. A Calculation field is just like a normal Text, Number, or Date field, except that its value is derived from the formula. The value is still stored just like the other field types.

[Sam Moffatt]

Sometimes you want to look at the contents of the parent linked form whilst you are inside the child record. If you’re using custom layouts this makes it a little easier to pick up and embed targeted values. I also have used calculation fields to project in the parent title to forms so that I can see it in the record list.

You can set a calculation field to calculate only once which should happen when the record is created. Whilst I’ve not directly tested this, in theory if you reference the parent form fields it should capture the value as that point in time and then lock it as uneditable. You would need to have created the record from the parent record and would only obviously only work for a single parent record.

A pattern I use when working with script fields is to have them update actual fields and then check to see if those fields have a value already. It means you can reset their value manually if you really want to or you could delete the value to have the script reset it. That allows you to “fix” things or change them easily. I also have a pattern where I use the script field itself to store state as well by return a JSON encoded value for the script field and then reading the script field back to check the previous state.

A common pattern for maintaining temporal records is effective dated records. If that sort of model is important to you then you could store a shadow copy of your form that is effective dated and then use a script field to replicate the current field values. It generally is more effort than it’s worth though and future dated records likely wouldn’t work properly.

Hope this helps!

[Author]

Posts