LIST OF RECORD WITH ASSOCIATED UNIQUE VALUES

[bolognesiedalla]

Hi everyone.
if I have a list of

INTERVENTIONS[ID]

where each record has an associated [PRODUCT] (ref type column in the PRODUCTS table).

Is it possible to obtain a list of INTERVENTIONS[ID] where there are no duplicates of [PRODUCT]?
I can't use the UNIQUE function because it would compare the INTERVENTIONS[ID] which are obviously different

Thanks to who helps me

[jaichith]

I think it can be done. But I want someone more information about your need. 

I couldn't understand this... Please elaborate.

"Is it possible to obtain a list of INTERVENTIONS[ID] where there are no duplicates of [PRODUCT]?
I can't use the UNIQUE function because it would compare the INTERVENTIONS[ID] which are obviously different"

Select(intervention ([id],true) .... This gives you distinct list

[WillowMobileSys]

How do you decide which record is the THE one to choose?  I think you need to give us more details of your goal and what you have built so far.  That understanding will influence what solutions we provide.

Presently, it doesn't make sense to just pick any row for each [PRODUCT].  That implies that the list of unique Products is what's important so why not just get the list of unique products used?

[bolognesiedalla]

@WillowMobileSys  @jaichith 

Basically I'm in this situation:

FERTILIZATION PLANS parent table

INTERVENTIONS child table
columns:
[ID] UNIQUEID column
[PRODUCT] reference to the product table
[DOSAGE] numeric column that expresses the quantity
[PRODUCT_SUM] numeric column that sums all the quantities of that product used in the fertilization plan.

I built a PRINT PLAN table
with column [ID_PLAN] of type ref to the FERTILIZATION PLANS table

with a Bot that creates the PDF of the fertilization plan with all the intervention lines and related dosages.

At the end of the template I have to create a summary table where there are:

1. list of products used without duplicates

2. for each product the sum of all the [DOSAGE] values ​​of the fertilization plan

So I thought I would get a unique list of INTERVENTIONS[ID] where I already have [PRODUCT] and [PRODUCT_SUM].
I just don't know how to get a unique value based on the [PRODUCT] value.

I have to get something like this:

Summary to get

INTERVENTIONS record

Maybe I'm taking the wrong approach...

[WillowMobileSys]

---

@bolognesiedalla wrote:

o I thought I would get a unique list of INTERVENTIONS[ID] where I already have [PRODUCT] and [PRODUCT_SUM].

---

Your post helps a lot!  I gather that either your Dosage and Product Sum values are ALWAYS the same OR you are using a similar row as a starting point and then adjust those values.

Either way you want to separate the "Planned Interventions" from the "Possible Interventions".  In other words have 2 tables.  "Planned Interventions" is your current child table.  "Possible Interventions" is here you store the UNIQUE list of combinations of Product, Dosage and Product Sum.

Add a Ref column in your "Planned Interventions" table that shows the list of "Possible Interventions".  When chosen, populate the Dosage and Product Sum from your "Possible Interventions" table into the "Planned Interventions" row.

If you have more than one possible row per Product (i.e. different Dosages), you could allow the user to pick the Product and then filter the "Possible Interventions" choices by that Product.

For any NEW entries of "Possible Interventions", new products or for different Dosages (if allowed), you would have the user do this from the dropdown list of "Possible Interventions".  You can configure it to show a NEW button to add these new rows.  See image.

I hope this helps!

[jaichith]

Nice...