@FLMike
Thanks for the response. I hope the following provides some additional details on what I'm doing. As for the reason for the boolean? It is an unlock function. I need to check if just one of 10 options is in any of 6 response options.
If a value shows up in the Approval Choice, and it is only one, for the selected record, it's valid and it allows access to another table.
The following is what I currently have:
// Collect active and approved Approval Choices Index field only
// Loads in App.OnStart as choices unlikely to change when app running.
ClearCollect(
colApprovedChoices,
ShowColumns(
Filter(
Choices',
And(
Choices = 1,
Status = 1
)
),
IDXApprvChoice
)
);
// Runs OnVisible. Check if record is valid, and approved.
// Collect Approved Record from Approvals table. Only Choices columns required.
ClearCollect(
colApprovalsCheck,
ShowColumns( // Produces Record with 2 columns: Name: IDXApprvChoiceN, Value: Number
LookUp(
'Approvals',
IDXRef = varNewApproval.IDXRefID //Current record to be checked
),
IDXApprvChoice1,
IDXApprvChoice2,
IDXApprvChoice3,
IDXApprvChoice4,
IDXApprvChoice5,
IDXApprvChoice6
)
);
// Concat columns to single record. Then split into single column
ClearCollect(
colApprovalsCheck,
ForAll(
Split(
Concat(
colApprovalsCheck,
ThisRecord.IDXApprvChoice1 & ";" &
ThisRecord.IDXApprvChoice2 & ";" &
ThisRecord.IDXApprvChoice3 & ";" &
ThisRecord.IDXApprvChoice4 & ";" &
ThisRecord.IDXApprvChoice5 & ";" &
ThisRecord.IDXApprvChoice6
),
";"
),
{IDApprv: Value}
)
);
// Test output to check if valid choice in selected record.
//
// Following is for testing result is valid and works
Text = If(!IsBlank(LookUp(colApprovalsCheck,IDApprv in colApprovedChoices)),true,false)
It's a bit of a rats nest, but it works and returns true/false.
Interestingly enough the ShowColumns() does return a record with the information as required:
Name Value
IDXApprvChoice1
IDXApprvChoice2
IDXApprvChoice3
IDXApprvChoice4
IDXApprvChoice5
IDXApprvChoice6 16
But I cannot workout how to extract the Value. In the collection it returns it as a single row.
I'm sure it can be refined, so any help is appreciated.
