Create list from column in same table

I have a table which keeps a record of incidents and follow-ups to those incidents. Each incident has a unique ID for who the incident belongs to, a timestamp, a Yes/No column to indicate if it is a new incident, and description of the incident. One of the questions I ask the user is if the new entry they are making is related to a prior incident. If the answer is yes, I would like a list to be presented to the user from which they can select the originating incident for that particular individual.

What is the correct way to create this list? I need the list to based on the current record’s ID for the individual and show the related times and descriptions of all originating incidents.

I’m not clear on what this means with regards to an ID for the incident and an ID for the person. I’ll just assume there is an Incident ID and a Person ID

I would add a Ref column named something like “Prior Incident”. Show_If hides it until the prior incident flag says “Yes”.

Then Set the Valid_If property in this new “Prior Incident” column to an expression like:

SELECT(Incidents[Incident ID], 
       AND([Person ID] = [_THISROW].[Person ID], 
                [Incident ID] <> [_THISROW].[Incident ID]
       )
)

This will provide you a dropdown list fo all PRIOR incidents for this Person. The [Incident ID] <> [_THISROW].[Incident ID] prevents the current Incident from showing in the list. Important if you need to EDIT the record.

TIP: To show more Incident info in the dropdown list, you CAN select more than one column as the Label OR you can create your own custom Label column with a Virtual Column and use that as the Label.

1 Like

Thank you! How do you “select more than one column as the Label”?

In the table column definitions, there is a Label property. Turn it on for all columns you want to be part of the display label. See the example pic below:

I am getting this error when following these instructions, " Table ‘Master’ had multiple non-image columns marked as ‘Label’, but only one non-image label is allowed. We removed the extra labels."

Also, how would the formula read with the additional column?

My apologies. I thought you could select more than one column. Seems you can only do that if the second is an image.

You can get around this by creating a Virtual Column that concatenates multiple columns together and then selecting that as the label.

Formula’s themselves are not impacted by what you choose as a label. A label is simply used as the display value - when appropriate.

This article might help to make it more clear

2 Likes

I probably do understand what you want.

Do you want to refer to the set of the rows out of the same table?
I assume you need to let the user pick up one of multiple previous report in the table.

In that case you create enumlist type and either validif constrain or suggested value, you refer to the same table and record the IDs.

Then create the virtual column to construct the select expression to return the list. Select(sametable[Key], in(xxxx, xxxxx))

It wil return list of rows based on selection within enum type your user selected.

I think you have field with name “Related incident report” or something, which

2 Likes