Building a doc for a Pharmacy: Single cell to lookup if record exists and if it doesn't exist allow me to enter it

Hey all!

I’m trying to help a local Pharmacy digitalise their collection process for medicine to help them deal with the huge amount of inbound they’ve had recently!

As you can see from the below/this doc link I’m almost there, but they have one request that I’m struggling to figure out -

As you’ll see, when you click to log a new prescription, we have an ‘existing email address’ field, and a ‘enter email address field’

Their staff are not tech savvy and are often in a rush, so they want us to consolidate these two fields into one - so that if an entry exists, it’ll populate the field, but if it doesn’t, they can add it into that field.

Can anyone please help me at all!? :slight_smile:

Thanks so much!


Here’s an embedded version:

Hello @Ben_Swanson!
This seems like a prefect task for a button, since you already have one to Log a new prescription, I think the way to go would be to pick the patient before pressing that button, then when you add a new row to the prescription you fill the patient with the selected one and the information you have on it, that way if there is no info they will be empty and ready to fill.

Another way, it could still be pick the patient first and then press the button (leaving it with the action you already have). Then in the Prescriptions Table make the Patient column a multiple select and the in the detail view you will get a toggle to show the Patient column as table. That way you can edit the info directly in the Patient Table.
Here is a doc showing how to accomplish the second way:


This is amazing, thank you!! Such an elegant solution to a problem I had been really struggling with!! As you can see, I tried to hack together a workaround which is now redundant haha such is the life of building a doc! Anyway, thank you!!

If you have another minute - I am struggling with another challenge on this doc -

As you can see, below that, I’ve tried to setup a system where the user can EITHER filter by patient OR they can filter by date range.

The issue is that I can’t figure out a way to make it so that whichever is used overrides and clear the other one because I can’t find anyway to make a ‘date range control’ editable by something else.

I’m not sure I did a good job explaining this, so here’s the two use case I’m trying to achieve -

  • Pharmacist wants to lookup which medicines are currently allocated to a patient but haven’t been collected

  • Pharmacist wants to see which medicines across all patients have not yet been collected

Hopefully that makes sense!?

Any thoughts or help you can provide are much appreciated, thank you!!

Just a quick update - I think I have now managed to solve my second issue, so don’t worry about it if you see it!


Oh! Excellent!
How did you accomplish it?
I was thinking of making it a table because controls cannot be overridden by buttons the problem is that there is no column type that with the same functionality as Date Range control.

Another way would be making some tricks in the table filter. If a Patient is selected ignore the Date Range control and when it is not use it as the filter

Actually, I thought I had solved it but turns out it needed more work! Images below show what I ended up with anyway - basically if the patient row is empty it filters based on the dates selected in the date columns (which are converted into numbers behind then scenes and then added to the filter). The buttons simply delete the date in the relevant section to ensure that there’s no confusion with the two filters.

I’m sure there was probably a much more elegant solution than this, but it seems to work, so I’m happy!



Sadly, I’ve got a new issue, I’ve done a new community post, here in case anyone that’s following this thread might be able to help out?

Thanks in advance for any thoughts!

Dear @Ben_Swanson,

Looks like you are concerned sharing the doc, something I can understand.

Therefore I advise to get in contact with support, several Codans have already offered to do their best to support you for this project.


Thanks Jean Pierre! No issue with sharing the doc, the link is in my first post :slight_smile:

Saul did a great job with helping with my first problem, this is a totally new issue, but as you suggest, I’ll reach out to support and see what they say too - just trying to get it rectified asap!