Forum Discussion

JamesTrory's avatar
JamesTrory
Qrew Assistant Captain
7 years ago

There are no compatible fields"" when creating Dynamic Form Rule

I'm creating a form on which are several lookup fields from another table. When a user makes a selection from the dropdown field [Name] (from another table) it looks up the value in [Type] (in the ...
App builders
Forms
Relationships
JamesTrory's avatar
JamesTrory
Qrew Assistant Captain
7 years ago
It's a multi-select text field.

I'm sure some context will help. It could be that I'm going about this all wrong.

I'm building a photo production app. [Name] is obviously the person's name. [Type] is the job role that they typically have, eg. Photographer, Digital Tech, Stylist, etc. There is also a field called [Rate] which looks up how much they cost per day.

Most people only have one role but some people might have two so that is why [Type] is multi-select. It also means that their [Rate] can vary.

What I'm trying to do in the form is use [Name] as a dropdown field for a user to find someone and then it will lookup their [Type] and their [Rate]. This works fine as a basic lookup because like I said, 95% of the [Name] choices will only have one [Type] and one [Rate].

Where it's getting overly complicated is that what if a person has two values in [Type]? How do we know which one they should get billed for and what the correct [Rate] is? This I believe requires user interaction.

So I had the idea of introducing a new field called [Type (Billed As)] which can be selected manually by the user to effectively 'overwrite' the default [Type] value if they have to.

Example - if the default [Type] is Digital Tech the user can say no, today they were billed as a Photographer and select that from [Type (Billed As)], overwriting the automatic selection of Digital Tech from [Type].

Of course I'm making this very hard on myself.
AlexCertificati's avatar
AlexCertificati
Qrew Cadet
7 years ago
It may be simpler (ha) to set up a many-to-many join table between Name and Type, and set up Type (Billed As) as a lookup to THAT table.

In other words, you have your people on Name and all your job roles on Type, and, say, Workers on a Worker table that is a child table to both Name and Type. EG Meg, Jo and Beth are on the Name table; Photog, Tech and Stylist are on the Type table; and you have Worker records that look something like:
Meg - Photog - [Rate]
Meg - Tech - [Rate]
Jo - Tech - [Rate]
Beth - Photog - [Rate]
Beth - Stylist - [Rate]

And then your Type (BIlled As) is a lookup to Worker records, so you can get everything you need the user to choose from into one dropdown when picking the person doing the job. And it's easy to roll up all of Meg's Worker info to her Name record, all of the Photog Worker info to the Photog Type record, and the Type (Billed As) records are grandchildren on both Meg and Photog records.

I think that's what I'd do, given how you've described your structure so far. One idea, anyway. Multi-select fields are not very easy to operate on.