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! Go to 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
.
Iโm very confused.
What is the point of this column? Is the user to choose the repair?
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
)
)
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
)
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.
@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.
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
.
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.
I wonder if itโs because there is no item separator? Try putting ", " minus the quotes in there and see if that changes anything.
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |