Re: How to count how many of the same record is linked to another
Hi Ben,
Thanks for getting back to me. I’ll elaborate.
So what we’re building is a system for patients, doctors, clinicians, pharmacists and suppliers to view, manage and update medicinal cannabis products.
Suppliers are able to add/update/remove their products. This the main functionality of the app at the moment. The idea is that clinicians and pharmacists can then view these products and based off the information they can recommend or prescribe them.
This is where indications come in - There are two types:
-
Supplier Recommended Indications
These are entered by the supplier from their findings -
Clinician Reported Indications (ClinROs)
These are entered by clinicians based on their findings. -
Then eventually we will have Patient Reported Indications
Patients will then be able to record their own personal experiences
Each of these indications have their own tables and are linked to the products table.
We currently have all the links setup and everything works and displays how it should. However we are trying to figure out a way to record the amount of times a particular indication has been given to a single product. When someone views a product they can see the indications given eg.
How it looks:
Product
Clinician Reported Indications
Pain, Headache, Ect
How we’d like it to look:
Product
Clinician Reported Indications
Pain(24), Headache(5), Ect
I’ve thought of several ways to try and achieve this but I’m not very familiar with automations and formulas so I haven’t been able to test. It’s easy to count the indications overall, but when you try and split it into counting a particular indication per product I can’t seem to figure it out. But there must be a way.
Any help is greatly appreciated, I hope this information provides you with some more insight into what we’re trying to achieve.
For some reason, I’ve been very invested in this problem since I first started playing around with it.
Let me toss you a possible implementation.
Tell me what you think. We’ll refine it a bit based on your feedback.
Here’s the sandbox I’ve been doing all of this in. I’ll keep working in here in regards to your post/question, but feel free to make a copy and toy with it as well. All of the data is just based on your screenshots and the rest is just filler.
My first recommendation is that you tweak your base design a tiny bit.
Something that immediately caught my attention is the separate tables you have for CRIs, SRIs, and eventually PRIs.
Now, since each record in all of those tables is a record of an individual indication, we can actually make a single table for all of our indications.
In my sandbox, I just created a single select field for the indications that designates them as being a CRI/SRI/etc.
Now, in the Products table, I’ve tweaked the structure a bit.
Don’t focus on my toying around and formatting experimentation. I’ll outline some of them below, as well as the reasoning behind some of them (if you’re curious).
Solving Your Problem
From what I understand all-in-all, you need a real-time count of the categories of SRIs and CRIs (eventually PRIs).
There are two separate count fields (and another hidden PRI field, since there’s no PRI data yet) that individually count the linked records of each record “type”.
When I created the Indications table, I created views for each category.
I then created linked record fields for each indication category and used that as the filter on the linked record field.
This allows you to separate the records that can be linked, whilst also maintaining a clean base data model by keeping all your indications in a single table.
From there, all I did was create count fields for each indication type and set the configuration as shown in the screenshot below.
That will then give you the up-to-date count of all the indications associated with a product.
It will also allow you to separate how many of each category there are.
Here’s the final product:
Okay, if you’re curious about my tweaks to the table layout, here’s a walkthrough of my decisions.
In the screenshots you shared, I noticed that you had a lot of products that had very similar names.
I probably totally misunderstood the overall implementation, but I’ve separated out dosage(?) data into two different percent fields. I noticed that you had some ratios, but I also saw the percentages, hence I left out the ratios for the sake of streamlining.
With those things separate, I used a formula to combine those fields to create a unique ‘tag’ for each record that highlights the differences in the products beyond just their names.
Here’s the formula if you’re curious.
It was just me messing around.
IF(
AND(
{Product}
),
{Product} & "\n" &
IF(
{THC},
"THC: " & ({THC} * 100) & "%",
"THC: ❌"
) & " | " &
IF(
{CBD},
"CBD: " & ({CBD} * 100) & "%",
"CBD: ❌"
) &
IF(
{Type},
"\n" & {Type}
),
"Missing Product Information"
)
I noticed you had a “flower” in one of the records, so I just went ahead and created a random product type field.
Again, no clue what your data looks like, so feel free to disregard it lol.
I’m trying to think of anything else, but I can’t think of any right now.
Again, take a look at the viewing link and lemme know if you’re curious for more, or if you have what you need.
