Populating and Enumlist Base Type Ref with a Condition

Scott_Hall
Participant V

I have a table Disposal Events and a table Service Location Repairs. Disposal Events references Service Location Repairs in a column named [Service Location Repair]. That column in table Disposal events is set up as an enumlist base type ref referencing the table Service Location Repairs in hopes of being able to select records as follows: Show service location repairs records with a [status] that equals 'destroyed". Show the records by the column [Timestamp] in Service Location Repair table.

From reading other posts I tried expression Select(Service Location Repair[Timestamp], in([Timestamp], [_thisrow].[Service Location Repair])) in the data validity valid if spot but it did not work. Tried a few variations with no luck. This expression also not addressing the filter [status] = "destroyed. Was trying to get at least the first iteration to function with no luck.

If I can get this to work, I think the app will full satisfy needs. This is hopefully the last challenge.

Anyone who is feeling charitable and could help with the expression would be much appreciated.

Solved Solved
0 15 1,214
1 ACCEPTED SOLUTION

Thank you for the helpful screenshots.

FILTER(
  "Service Location Repairs",
  ("destroyed" = [Status])
)

FILTER() will return a list of Ref values for rows in the Service Location Repairs table where the Status column value is destroyed.

View solution in original post

15 REPLIES 15

Steve
Participant V

Iโ€™m very confused.

What is the point of this column? Is the user to choose the repair?

@Steve

Yes. For context a service location repair represents box collection events from various locations on various days that have to be disposed of. So the purpose of the column is to allow the user to select the service location repairs that are being sent for disposal. So it is a way of associating a disposal event with a batch of repairs. Timestamp column in the repair table is written on the box so that each box collected has an individual id and is associated with a disposal event.

I collect boxes at individual locations but dispose of them in a batch and need a way to associate the individual collections of boxes with a single disposal event without reopening each individual repair record.

The table service location repair should really be named Collection Events. I will rename tables and clean things up for final version.

I hope this answers and thank you for engaging.

Okay, I think I got it.

Try:

IFS(
  ISNOTBLANK([Service Location Repair]),
    SORT(
      SELECT(
        Service Location Repairs[Timestamp],
        AND(
          ([_THISROW].[Service Location Repair] = [RowKey]),
          ("destroyed" = [Status])
        )
      ),
      TRUE
    )
)

replacing RowKey with the name of key column of the Service Location Repairs table.

@Steve Double checked column and table names. Got this message.

Cannot compare List with Text in ([Key].[Service Location Repair] = [Key])

*Key for service location repair is โ€œkeyโ€

[_THISROW].[Service Location Repair] = [Key]

Or that is the error message you are getting with this in your expression?

Maybeโ€ฆ

IFS(
  ISNOTBLANK([Service Location Repair]),
    SORT(
      SELECT(
        Service Location Repairs[Timestamp],
        AND(
          (IN([RowKey],[_THISROW].[Service Location Repair])),
          ("destroyed" = [Status])
        )
      ),
      TRUE
    )
)

@Steve @Bahbus

This expression shows as valid in the valid if part of the data validity. However the column is not showing in the disposal events form. When I remove the expression, the column shows but nothing is populating the enumlist.

IFS(
ISNOTBLANK([Service Location Repair]),
SORT(
SELECT(
Service Location Repair[Timestamp],
AND(
(IN([Key],[_THISROW].[Service Location Repair])),
(โ€œdestroyedโ€ = [Status])
)
),
TRUE
)
)

Note, this expression could significantly impact sync time.

IFS(
โ€ฆThis statement is false:
โ€ฆ(The value of column โ€˜Service Location Repairโ€™) is empty
โ€ฆSORT(
โ€ฆThe list of values of column โ€˜Timestampโ€™
โ€ฆfrom rows of table โ€˜SERVICE LOCATION REPAIRโ€™
โ€ฆwhere this condition is true: (ALL these statements are true:
โ€ฆ1: (The value of column โ€˜Keyโ€™) is one of the values in the list (The value of โ€˜Service Location Repairโ€™ from the row referenced by โ€˜Keyโ€™)
โ€ฆ2: (โ€œdestroyedโ€) is equal to (The value of column โ€˜Statusโ€™))
โ€ฆโ€œTRUEโ€))

How about this:

SORT(
  SELECT(
    Service Location Repairs[Timestamp],
    ("destroyed" = [Status])
  ),
  TRUE
)

@Steve

That shows the list. However I get the yellow triangle warning by each result in the list. If you select a batch and save it says the โ€œentry is invalidโ€ (under the column in red) and wonโ€™t allow the save.

So tantalizing close. Been trying for months to solve this piece of the puzzle.

Iโ€™m guessing a broken reference but I have no idea how. I have a reference from Repairs to Disposal and visa versa.

Scott_Hall
Participant V

@Bahbus Yes. That is where the expression is plugged in. Valid if in the data validity section.

Yeah I deleted it, because I lost my train of thought and forgot that is what were targeting in the first place.

So lets recap. You have an EnumList of base type Ref (and as a note, I believe Iโ€™ve seen @praveen or another Dev say Ref EnumLists are fragile at best). Weโ€™re trying to get the Valid If working. So far all attempts either cause the list to be empty or broken Refs. If you can submit screenshots of some sample data and column structure, I think weโ€™ll be able to help better. Itโ€™s probably something silly that weโ€™re all missing.

@Bahbus

The recap is correct. Iโ€™ve been in a months long attempt to get these individual collection events (repairs) associated with a single disposal event. A disposal form with the EnumList ref type base is the current effort. (applying a single signature to the individual collection records at one time was the other effort I gave up on)

Here are some screen shots. Iโ€™m currently experimenting on a deployed app. Probably should copy and test on a non deployed but it is only used in my small companyโ€ฆfor now.

Thank you for the helpful screenshots.

FILTER(
  "Service Location Repairs",
  ("destroyed" = [Status])
)

FILTER() will return a list of Ref values for rows in the Service Location Repairs table where the Status column value is destroyed.

@Steve

Bingo. Wow. Thank you! That works. Now I can make the association of many collection events to one disposal event with a signature.

Thank you again.

Bahbus
Participant V

I wonder if itโ€™s because there is no item separator? Try putting ", " minus the quotes in there and see if that changes anything.

@Bahbus

Nope.

Top Labels in this Space