Select distinct from existing table into new table...

leeca88 · 07-20-2021 08:03 PM

Hello, I have the following two tables set up and working:

Contract - A table with contract as unique key
REF_ROWS("Nomination", "Contract")
Nominations (One contract - Many nominations)

In my Nomination table, I have the following sample data:

Nomination_ID |    Date              | Contract  | Vessel Name |  Quantity
    abcde123       20/07/2021          1                A           1,000
    defgh234       20/07/2021          2                A           2,000
    hijkl456       21/07/2021          3                B           3,000

I would like to add, say, another column, Quality, for distinct values of Date and Vessel Name.

In SQL:

select
    distinct date, vessel_name
from nominations

The results of this query would be:

Date       | Vessel Name
20/07/2021   A
21/07/2021   B

And from here, the values for the new column Quality would only need to be added once per date and vessel, instead of multiple times in the Nomination table above.

Now, the user only has to update the Quality column twice:

Date       | Vessel Name | Quality
20/07/2021   A             1%
21/07/2021   B             2%

Instead of 3 times (per Nomination ID😞

Nomination_ID |    Date              | Contract  | Vessel Name |  Quantity | Quality
    abcde123       20/07/2021          1                A           1,000     1%
    defgh234       20/07/2021          2                A           2,000     1%
    hijkl456       21/07/2021          3                B           3,000     2%

To try and summarize:

I am trying to add another column of information to the existing table Nominations, and want to prevent the user from adding the same information multiple times for a given distinct group of Date | Vessel Name.

Am a bit confused as to how I would accomplish this in AppSheet - do I need to create a new table Nominations_With_Quality in Google Sheets to house this new column? Or is there any way I can re-use my existing table Nominations

Marc_Dillon

I’ve read this post about 5 times now, but I can’t understand exactly what you want.

When you say you want distinct values of Vessel and Date, from your example data, are you expecting {A,B} and {20/7/21,21/7/21} to be returned? Should these Lists be in separate columns, or what?

This sentence in fact make your point even more confusing. I don’t even know how to ask any question to clarify it. Could you perhaps say it in a different way?

It’d probably help if you very explicitly showed what result you expect for the new column, based on the above sample data.

You are free to add new columns to your GSheet (then “regenerate” the Table), or you can add Virtual Columns.

leeca88

Thank you for taking the time to reply to my post - I have read your comments and updated the post, hopefully it is clearer now what I am trying to accomplish.

Marc_Dillon

Ahhh! I thought you were wanting to save these distinct groups as data.

Ok. To start we need an expression that’ll return TRUE if there is no record with the same vessel/date combo that already has a [Quality] value.

ISNOTBLANK( FILTER(
  Nominations ,
  AND(
    ISNOTBLANK( [Quality] ) ,
    [date] = [_THISROW].[date] ,
    [vessel] = [_THISROW].[vessel]
  )
) )

Then you can probably just stick that into Editable_if for the [Quality] column.

Does that work for you?

leeca88

Hey Marc,

I’ve just had time to give this a go, and the formula works, although I had to change ISNOTBLANK to ISBLANK.

ISBLANK( FILTER(

  Nomination, 
  AND(
    ISBLANK( [Oil Content] ), 
    [Nomination Date] = [_THISROW].[Nomination Date],
    [Vessel] = [_THISROW].[Vessel]
  )
 
) )

From my understanding, this column is editable if this table, Nomination, is filtered for rows where there are no quality values for a given combination of date / vessel.

I have Show, Editable, Require toggled on for the column “Oil Content”.
The column is newly created and has not been populated with any values.

I could not get the “Oil Content” to show up in the detail view:

I clicked UX > + New View > Form, and I can see the Oil Content field show up in the main view
After filling in the Oil Content field, when I click on the detail view, the Oil Content field is not shown, and unable to be edited anywhere

Kindly let me know what I might have missed, in case my explanation was not clear, I can also post screenshots or share a copy of my app

Select distinct from existing table into new table and append new column