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.
