LinkedIn
Twitter
Eveningโฆ
ValidIfโฆ???
Iโd like to prevent my survey_Form saving if any asset in [asset_name] also exists in the survey table [asset_name] column. This is to prevent a new survey starting from this survey_Form if a survey already exists.
Two things to note:
-
[asset_name] is an EnumList Ref Type that uses a ValidIf to produce itโs list from the asset table.
-
When the survey_Form is saved an action extracts each name from [asset_name] and creates a new row for each in the survey table - so the survey table will always only have one name in [asset_name] even though itโs an EnumList in the form.
.
.
.
The [asset_name] ValidIf is already in use so the approach Iโve been trying is for a separate column [validate_this_survey] to have a ValidIf that says:
NOT(
IN(
ANY([asset_name]),
SELECT(
survey[asset_name],
AND(
[LCS] = [_THISROW].[LCS],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey]
))
)
)
.
Iโm trying to force an error if any asset in [asset_name] also exists in the survey table but Iโm failing.
Any assistance would be greatly appreciated.
Thanksโฆ 
.
.
survey_Form
survey table
Hi @Steve,
Thanksโฆ Iโve now made some progress on the [validate_survey] column.
[validate_survey] now always matches [asset_name] except when the existing survey asset names from the survey table have been subtracted.
When that happens, [validate_survey] appears blank/empty. So I tried the following to trigger the error:.
.
[validate_survey] Valid If
ISNOTBLANK([validate_survey])
[validate_survey] Initial Value
[asset_name]
-
SELECT(
survey[asset_name],
AND(
[LCS] = [_THISROW].[LCS],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
NOT([branch_info] = "computed")
))
.
Sorry but thereโs something about Valid If that I donโt quite getโฆ Please could you point out where Iโm going wrongโฆ? 
.
.
Hi @Steve,
Thanks for your detailed responseโฆ
I am currently using a few Valid If expressions in my forms as you suggested and I use Initial Value expressions as well to pre-populate some Enum Lists.
I may have asked my original question badlyโฆ
What Iโm trying to do is invoke a โData Validityโ situation so that I can force an โInvalid Value Errorโ message (preventing a user from saving the form) until they select different form options that release the โError Triggerโ i.e. the [asset_name] column.
I still havenโt figured this one out!
.
.
Based on your previous suggestion, I went back into my [asset_name] Valid If and inserted a list to subtract any previously surveyed asset names which modifies the formโs previous behaviour. Now the form will only show asset names that have not been started/finished previously. Thatโs great thanks!
.
-SELECT(survey[asset_name],
AND(
[LCS] = [_THISROW].[LCS],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[sub_asset] = [_THISROW].[sub_asset],
[select_asset_type] = [_THISROW].[select_asset_type],
NOT([_THISROW].[branch_info] = "computed")
))
.
The result is that the [asset_name] column is BLANK in the form when it comes across a scenario where something was surveyed before i.e.
6 assets were proposed in the Enum List thenโฆ
6 previously surveyed assets were discovered and subtracted andโฆ
0 assets remain in Enum List and it disappears in the form (BLANK?)
.
Hereโs an example where CAT6A has no asset names to display:
.
.
And hereโs one where Fibre does have asset names to display:
.
.
.
.
.
.
You may regret having asked this one as the inefficiency of these expressions is either going to make you laugh or cry so loud you may do yourself a mischiefโฆ 
.
.
.
.
[asset_name] Valid If
IFS(
[survey_type] = "Node",
ORDERBY(
SELECT(asset[id],
IN([id],
SORT(
UNIQUE(
SELECT(rf_design[Source Asset ID],
AND(
[lcs] = [_THISROW].[lcs],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[sub_asset_name] = [_THISROW].[sub_asset],
[source_asset_type] = [_THISROW].[select_asset_type],
NOT([_THISROW].[branch_info] = "computed"),
NOT(
AND(
LEFT([sub_asset_name], 3) = 'HPR',
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
))
))
+SELECT(rf_design[Destination Asset ID],
AND(
[lcs] = [_THISROW-2].[lcs],
[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
[sub_asset_name] = [_THISROW-2].[sub_asset],
[destination_asset_type] = [_THISROW-2].[select_asset_type],
NOT([destination_asset_type] = 'Cabinet'),
NOT([_THISROW].[branch_info] = "computed"),
NOT(
AND(
LEFT([sub_asset_name], 3) = 'HPR',
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
)),
NOT(
AND(
[main_asset_name] = [sub_asset_name],
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
))
))
-SELECT(survey[asset_name],
AND(
[LCS] = [_THISROW].[LCS],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[sub_asset] = [_THISROW].[sub_asset],
[select_asset_type] = [_THISROW].[select_asset_type],
NOT([_THISROW].[branch_info] = "computed")
))
),FALSE
)),TRUE
),[asset_name]
),
[survey_branch_number] = 1,
ORDERBY(
SELECT(asset[id],
IN([id],
SORT(
UNIQUE(
SELECT(rf_design[Source Asset ID],
AND(
[lcs] = [_THISROW].[lcs],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[sub_asset_name] = [_THISROW].[sub_asset],
[source_asset_type] = [_THISROW].[select_asset_type],
NOT([_THISROW].[branch_info] = "computed"),
NOT(
AND(
LEFT([sub_asset_name], 3) = 'HPR',
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
))
))
+SELECT(rf_design[Destination Asset ID],
AND(
[lcs] = [_THISROW-2].[lcs],
[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
[sub_asset_name] = [_THISROW-2].[sub_asset],
[destination_asset_type] = [_THISROW-2].[select_asset_type],
NOT([destination_asset_type] = 'Cabinet'),
NOT([_THISROW].[branch_info] = "computed"),
NOT(
AND(
LEFT([sub_asset_name], 3) = 'HPR',
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
)),
NOT(
AND(
[main_asset_name] = [sub_asset_name],
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
))
))
-SELECT(survey[asset_name],
AND(
[LCS] = [_THISROW].[LCS],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[sub_asset] = [_THISROW].[sub_asset],
[select_asset_type] = [_THISROW].[select_asset_type],
NOT([_THISROW].[branch_info] = "computed")
))
),FALSE
)),TRUE
),[asset_name]
)
-SPLIT(survey[planned_asset_id], ",")
-[branch_A_assets]
-[branch_B_assets]
-[branch_C_assets]
-[branch_D_assets]
-[branch_E_assets],
[survey_branch_number] > 1,
SPLIT(
SELECT(
survey[survey_asset_id],
AND(
[survey_type] = "Cable",
[LCS] = [_THISROW].[lcs],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[sub_asset] = [_THISROW].[sub_asset],
[select_asset_type] = [_THISROW].[select_asset_type],
[survey_branch_number] = [_THISROW].[survey_branch_number],
[branch_info] = "new branch"
)),
","
)
-SPLIT(
SELECT(
survey[planned_asset_id],
AND(
[survey_type] = "Cable",
[LCS] = [_THISROW].[lcs],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[sub_asset] = [_THISROW].[sub_asset],
[select_asset_type] = [_THISROW].[select_asset_type],
[originating_branch] = [_THISROW].[survey_branch_number],
NOT([branch_info] = "computed")
)),
","
)
-SELECT(survey[asset_name],
AND(
[LCS] = [_THISROW].[LCS],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[sub_asset] = [_THISROW].[sub_asset],
[select_asset_type] = [_THISROW].[select_asset_type],
NOT([_THISROW].[branch_info] = "computed")
))
-[branch_A_assets]
-[branch_B_assets]
-[branch_C_assets]
-[branch_D_assets]
-[branch_E_assets]
)
.
.
.
.
[asset_name] Initial Value
IFS(
[survey_branch_number] = 1,
ORDERBY(
SELECT(asset[id],
IN([id],
SORT(
UNIQUE(
SELECT(rf_design[Source Asset ID],
AND(
[lcs] = [_THISROW].[lcs],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[sub_asset_name] = [_THISROW].[sub_asset],
[source_asset_type] = [_THISROW].[select_asset_type],
NOT([_THISROW].[branch_info] = "computed"),
NOT(
AND(
LEFT([sub_asset_name], 3) = 'HPR',
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
))
))
+SELECT(rf_design[Destination Asset ID],
AND(
[lcs] = [_THISROW-2].[lcs],
[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
[sub_asset_name] = [_THISROW-2].[sub_asset],
[destination_asset_type] = [_THISROW-2].[select_asset_type],
NOT([destination_asset_type] = 'Cabinet'),
NOT([_THISROW].[branch_info] = "computed"),
NOT(
AND(
LEFT([sub_asset_name], 3) = 'HPR',
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
)),
NOT(
AND(
[main_asset_name] = [sub_asset_name],
[source_asset_type] = 'Fibre',
[destination_asset_type] = 'HPR'
))
))
),FALSE
)),TRUE
),[asset_name]
)
-SPLIT(survey[planned_asset_id], ",")
-[branch_A_assets]
-[branch_B_assets]
-[branch_C_assets]
-[branch_D_assets]
-[branch_E_assets],
[survey_branch_number] > 1,
SPLIT(
SELECT(
survey[survey_asset_id],
AND(
[survey_type] = "Cable",
[LCS] = [_THISROW].[lcs],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[sub_asset] = [_THISROW].[sub_asset],
[select_asset_type] = [_THISROW].[select_asset_type],
[survey_branch_number] = [_THISROW].[survey_branch_number],
[branch_info] = "new branch"
)),
","
)
-SPLIT(
SELECT(
survey[planned_asset_id],
AND(
[survey_type] = "Cable",
[LCS] = [_THISROW].[lcs],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[sub_asset] = [_THISROW].[sub_asset],
[select_asset_type] = [_THISROW].[select_asset_type],
[originating_branch] = [_THISROW].[survey_branch_number],
NOT([branch_info] = "computed")
)),
","
)
-[branch_A_assets]
-[branch_B_assets]
-[branch_C_assets]
-[branch_D_assets]
-[branch_E_assets]
)
.
.
Cheersโฆ
Hi @Steve,
Agreedโฆ Please ignore everything aboveโฆ Starting overโฆ 
What Iโm trying to do is invoke a โData Validityโ situation so that I can force an โInvalid Value Errorโ message (preventing a user from saving the form) until they select different form options that release the โError Triggerโ
For example:
If the result of an expression in [Column A] is <=0
Then [column B] should have a (Data Validity) โInvalid Value Errorโ message and prevent a user from saving the form until [Column A] is no longer <=0
I can deal with the expressions/conditions that create or resolve [Column A].
Iโm having difficulty with [Column B]
Cheersโฆ
Hi @Steve,
I think Iโve got it now.
It looks like I was making this more difficult than it needed to be.
Apologies for the wild goose chaseโฆ 
Iโll go and apply this in my app nowโฆ Thanks for your timeโฆ 
.
.
Error Not Triggered
Error Triggered by [data] < 1
[validity_checker] column
[data] column
