Solved: Tutorial: Forms on SQL Server Table with GUID column and a custom validator as workaround

Hi all,

as you might know. As of when writing this post, Powerapps forms do not support SQL Server GUID fields as native funktion. The form will recognize a GUID field but it will show an error ones you submit the form. A workaround for this is eg. explained here:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Adding-a-new-record-to-SQL-Server-where-there-is-a/td-p/322139

Unfortunately the validation for required form fields only seems to work if SubmitForm function is used. So If we use the workaround built in validation is not happening as far as I can tell. So I came up with a little hack to build a onw validation, while reusing the normal Form Controls Like DataCards and so on.
Here is my example form

First for me it is important to change the names of the form controls to make things a bit easier to understand. I will do this example on the JobName input field...

Go with names that fit you fancy and that you easily know which control is what...
2 collections will be used to manage the validation process.

Ok lets start. Validation should happen when the user tries to submit the form.

Submit button OnChange function

Explanation:
First the collection is created that holds all controls that should be validated. ClearCollect is used so the collection will be reinitialized every time the users submits the form. This Collection will minimize the work we must do later on. Since we will use it to "loop" over it so we do not have do add a complete validation block for every input filed..

ClearCollect(
 coll_ValidatorFormNewElementReport,
 {
// Use the real refrence to the control not only the name here. Powerapps whill add a color if you did it right
 element: jobname_formNewElementReport_DataCardValue, 
// The name must mach the 'DataField" property of the DataCard
 parent_name: "jobname"
 },
 {
 element: troubleshooting_formNewElementReport_DataCardValue,
 parent_name: "troubleshooting"
 },
 {
 element: job_desc_formNewElementReport_DataCardValue,
 parent_name: "job_desc"
 }
);

Next a collection is created/cleared that will hold the validation results. I was thinking about to add the validation result to the first Collection but when looping (ForAll function...) Powerapps does not allow to change the same collection in the loop. Most likely for consistency reasons

Clear(coll_valideStateFormNewElementReport);

Last step is to actually do the validation. As mention the validation routine itself is completely generic. We do not have to alter it if more fileds need validation...

ForAll( // Do this for all rows in the Collection that contains the controls for validation
 coll_ValidatorFormNewElementReport,
 If(
// If the Text of the control is NOT Blank has no Error or has no empty string
 ThisRecord.element.Text <> "" && !IsBlankOrError(ThisRecord.element.Text),
// TRUE -> add a postive (1) result to the result collection
 Collect( 
 coll_valideStateFormNewElementReport,
 {
 input: ThisRecord.parent_name,
 valide: 1
 }
 ),
// ELSE add a negative(0) result to the result collection
 Collect(
 coll_valideStateFormNewElementReport,
 {
 input: ThisRecord.parent_name,
 valide: 0
 }
 )
 )
)

I left out the part of the submit button that actually does the write to the DataSource/SQL Server for now. So it is less confusing

The Result collection will look something like this

That is the first part of the validation logic.

Lets continue to make the validation results visible.
By default every form filed / DataCard contains a predefined Error Text label

To make the predefined field visible and have the correct message adjust 2 properties: Visible and Text

------------------------------------------
Visible:
------------------------------------------

If( //there is an entry for this datacaed in the result collection
 CountRows(
 Filter(
 coll_valideStateFormNewElementReport,
 input = Parent.DataField
 )
 ) > 0,
// Then
 If( //If not valid(0) true else false
 LookUp(
 coll_valideStateFormNewElementReport,
 input = Parent.DataField // to make this reusable without changing names or something the collection uses the same entry as the datacard datafiled :)
 ).valide = 0,
 true,
 false
 ),
 false
)

------------------------------------------
Error Text:
------------------------------------------
// to make this reusable without changing names or something uses the DisplayName of the datacard :)
Parent.DisplayName & " - is required!"

The only thing that needs to be done is to clear the error message once the user enters a value in the required field.
For this the OnChange property of the input filed is used:

// This is the same logic as in the submit button only difference is the "Self" reference to the text
If(
 Self.Text <> "" && !IsBlankOrError(Self.Text),
 Patch(
 coll_valideStateFormNewElementReport,
 LookUp(
 coll_valideStateFormNewElementReport,
 input = Parent.DataField
 ),
 {
 input: Parent.DataField,
 valide: 1
 }
 ),
 Patch(
 coll_valideStateFormNewElementReport,
 LookUp(
 coll_valideStateFormNewElementReport,
 input = Parent.DataField
 ),
 {
 input: Parent.DataField,
 valide: 0
 }
 )
)

This way the error will disapear once the uses has entered something in the field.

Remark: OnChange will only fire if one leaves the input fieled. It is not enough to type something as long as the focus remains on the field.

Finally the last thing is to add the actual Patch command, mention in the GUID thread, as a conditional to the Submit Button.
Logic is:

If there is at lease one row that is not valid (= 0) in the result array. Do not submit the form to the database.

If(
 CountRows(
 Filter(
 coll_valideStateFormNewElementReport,
 valide = 0
 )
 ) > 0,
 "NOK", // usually "false" here
 "OK" // Patch fucntion here - to write to DB 
)

I hope you could follow my approach. If not feel free to ask questions or suggest improvements!!

Code on!!!

Best

Jan aka MasterB ;o)

Categories:

Using Apps Using Formulas

Connecting to Data