I have tried everywhere to find the answer to this one and I am now going slightly crazy……………….
in summary, when I allow my user to add a new record I want to set the value of one of the fields (columns) based on a relation (in order to maintain a link) rather than have the user chose.
In this specific case a PATIENT is related to a single PRACTITIONER which they cannot change. The PATIENT can add updates on their progress and so is also related to multiple UPDATES. When the PATIENT adds a new UPDATE (which Glide beautifully relates to them automatically) I also want this UPDATE to relate to the USER’s current PRACTITIONER (for future reporting purposes). I cannot see how to “automate” this without asking the user to select the PRACTITIONER manually.
What is the logic you want to apply here in assigning the specific practitioner? Like are they free, are they being rated highly by previous customers or something?
Hi. There is no logic whatsoever because the patient can only have a single practitioner at any one time. I just want to record who that current practitioner is at the point the patient submits an update i.e. I want a link to the patient’s practitioner in a column within the update. So when I review the list of updates not only can I see the patient but also who their practitioner was at the time the update was submitted. Make sense?
New updates via the patient is recorded in a new table, let’s call it “Updates”
In the “Updates” table there is the email of the patient, make a single relation matching that with the email in the original table where you assigned the patient to the practitioner. Use a lookup column to pull the info about the practitioner to the “Updates”.
If you need to view more information about the practitioner than just the email, make a relation matching that email you just pulled back from the lookup to the practitioner table and get their names, images etc. via the lookup.
Will the patient’s practitioner ever update or change? I know you said a patient can only have one practitioner at any given time, but can the patient switch their practitioner?
Ha! I’ve just seen this issue myself funnily enough.
A patient can only have one practitioner at any time BUT it is possible that the practitioner for the patient may change over time. When the patient adds a new update row, I therefore need to store the practitioner at that point in time. So a lookup won’t give the desired result since it’ll effectively “go back” and change a previous update.
I figured! But @ThinhDinh’s approach is pretty much the answer, you just need to capture the data that is not dynamic.
Depending on what sheet you are placing your button, you can pull in the current user’s email using a template column.
Then, on that same sheet, create the relation and lookup of the patient’s practitioner. Just make sure this lookup is not on the actual ‘Updates’ sheet, but on the sheet where the button for your form will be placed.
Doing this, when you create your form, you can pull in the values from that sheet and automatically fill in the patient’s practitioner at that time. It will be a standard value and not dynamic data.
Will try to add screenshots in a few, but that should work.
I have been adding a new patient update using the “built-in” ability to add a new item to a list. So essentially the patient is viewing their list of updates then clicks the “+” in the top-right to add a new one.
I think you’re suggesting that I move the “add update” functionality to a “button” which shows a form for adding. And within that form I can pull data from the patient but still post to the updates sheet?
I’ve not that used that technique before but am I on the right lines?
Ok. I think I’ve solved this. By using a button and form to add the new patient update I had access to a bunch of columns which I could then use to “force” a value into my new record.
Previously, using the “built-in” add functionality directly off the updates list, these columns were not available for use.
I’m not sure why, and I’d love to understand why, but I do have a working solution. Thanks guys for pushing me in the right direction.
Only the form has the functionality to take values in the previous screens to fill the destination columns, the built-in add functionality doesn’t offer those.
To add to this, the Add option does not show the additional columns because the add is not attached to a specific detail row like the form button is. There is no data for the add option to grab because you aren’t viewing a specific detail row.
I’m new to Glide (but not so new to dev and data) and I have to say that the support / community in here is incredible. I actually feel productive. Thanks again all. I look forward to reciprocating as my knowledge increases.