Data Validation

[Anonymous]

Hi All, 

This is hard to explain, so I will try my best. 

I have Textinput3 with a search icon. OnSelect of the search icon I want to validate the data against an excel table. 

The excel headers are as follows:

StaffID StaffIDError StaffIDVIP StaffIDVIPError 

If the staff ID is in StaffID or StaffIDVIP , I want it to navigate to the relevant form - The below works for this part:

If(TextInput3.Text in Table1.StaffID, Navigate('Order Equipment_nonVIP',ScreenTransition.Fade)) && If(TextInput3.Text in Table1.StaffIDVIP, Navigate('Order Equipment_VIP',ScreenTransition.Fade)) && Reset(TextInput3)

I now need 2 things to happen. 

If the staff ID is in StaffID or StaffIDVIP, then I only want it to navigate to the form, if there is not a status in wither StaffIDVIPError  or StaffIDError columns. If there is a status in the StaffIDVIPError  or StaffIDError columns, then I want it the status on the columns to show as an error message. 

Happy to discuss alternative options, if there is any why this could happen. 

Thanks in advance 🙂 

[Drrickryp]

Hi @Anonymous 

If you could post a screenshot of your data table, it would help me find a solution for you.   However, based on your description, either a dropdown or combobox control would probably be better than a textinput control. If your categories are exclusive of each other, perhaps you could organize your data with a column for ID and a second column for Status like (regular, vip, regularerror, viperror)

[Anonymous]

Hello, @Drrickryp 

I have attached a screenshot example of my table.

The reason I had selected a textinput, if because I do not want users to be able to see other users information.

Any advise would be great, thanks.

[v-yutliu-msft]

Hi @Anonymous ,

Actually, I do not fully understand what do you want.

Could you describe more clearly about

1)"a status in the StaffIDVIPError  or StaffIDError columns"?

What is "status"?

2)"If the staff ID is in StaffID or StaffIDVIP, then I only want it to navigate to the form, if there is not a status in wither StaffIDVIPError  or StaffIDError columns. If there is a status in the StaffIDVIPError  or StaffIDError columns, then I want it the status on the columns to show as an error message. "?

What exactly the conditions are?

3)what control do you want to use to validate? just a textinput3?

Based on the screenshoot that you provided, you only have four columns in your excel table.

I understand that you want to check whether data in textinput3 exists in StaffID or StaffIDVIP. What about the rest of two columns?

I assume that:
you only want to use textinput3 to validate.

you want: data in textinput3 exists in StaffID or StaffIDVIP, data in textinput3 not exists in StaffIDVIPError  or StaffIDError.->navigate 

data in textinput3 exists in StaffIDVIPError  or StaffIDError->show error (however, I do not understand where do you want to show error)

If so, you could use formula like this:

If(
(!IsEmpty(Filter(Table1,TextInput3.Text in StaffID))||
!IsEmpty(Filter(Table1,TextInput3.Text in StaffIDVIP)))&&
(IsEmpty(Filter(Table1,TextInput3.Text in StaffIDVIPError))||
IsEmpty(Filter(Table1,TextInput3.Text in StaffIDError))),
Navigate(....),
(!IsEmpty(Filter(Table1,TextInput3.Text in StaffIDVIPError))||
!IsEmpty(Filter(Table1,TextInput3.Text in StaffIDError))),
error formula
)
//please replace with the screen that you want to navigate to and the error formula that you want

Best regards,

[Anonymous]

Hi @v-yutliu-msft, 

I have changed the columns to see if it works better. 

I have 2 columns now, staff ID and StaffIDStatus. If the staff ID is in StaffID column and the StaffIDStatus is blank, then I want it to navigate to a form.

If the staff ID is in StaffID column but the StaffIDStatus says VIP, then it navigates to another form. 

If it is not in the staffID coumn, then I want it to show an error message.

I am hoping this will make it easier. 

Thanks 

[Anonymous]

HI @v-yutliu-msft,

I have got it to do what i need by using the following: 

If(TextInput3.Text in Table1.StaffID, Navigate('Order Equipment_nonVIP', ScreenTransition.Fade)) & If(TextInput3.Text in Table1.StaffIDVIP, Navigate('Order Equipment_VIP', ScreenTransition.Fade)) & If(TextInput3.Text in Table1.Exclusions, Notify("errormessage"), Error) & If(TextInput3.Text in Table1.OnInventory, Notify("errormessage"), Error) & If(IsBlank(TextInput3), Notify("Please enter a valid staff ID"), Error) & Reset(TextInput3)

I now need that if it is not in any of the columns, StaffID, StaffIDVIP, Exclusions, OnInventory, then Notify("errormessage"), Error)

Many Thanks,

[Drrickryp]

If(
    IsBlank(
            Lookup(Table1,Textinput3.Text=StaffID
             ), Notify("ID not found",NotificationType.Error
                 )
      ),
      Lookup(Table1, Textinput3.Text = StaffID && IsBlank(StaffIDStatus),
       ), 
          Navigate(someform
           ),      
       Lookup(Table1,Textinput3.Text=StaffID && StaffIDStatus = "VIP"
        ), 
       Navigate(Order Equipment_VIP',ScreenTransition.Fade
        ),
       Navigate('Order Equipment_nonVIP',ScreenTransition.Fade
        )
)

If() function evaluates statements until it finds one that reduces to true and then stops, so the first lookup will stop if the StaffID column is blank, if not then it evaluates the staffIDStatus for a blank, if it finds one it will navigate to a form, it then evaluates the StaffIDStatus column for "VIP" and if it is found, navigates to that form.  If all the other options are false, then the last one must be true and it will navigate to the nonVIP screen.

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

Please do not use "TextInput3.Text in Table1.StaffID" to justify whether there's value in a field.

You need to use filter function to filter whether there's record meet the reuqirements, then use IsEmpty function to justify where there's record that meet requirements.

Formula should be like this:

If(IsBlank(TextInput3.Text),
   Notify("Please enter a valid staff ID"), Error),
   If(
      !IsEmpty(Filter(Table1,TextInput3.Text in StaffID)),
      Navigate('Order Equipment_nonVIP', ScreenTransition.Fade),
     !IsEmpty(Filter(Table1,TextInput3.Text in StaffIDVIP)),
      Navigate('Order Equipment_VIP', ScreenTransition.Fade),
      IsEmpty(Filter(Table1,TextInput3.Text in StaffID||
                            TextInput3.Text in StaffIDVIP||
                            TextInput3.Text in Exclusions||
                            TextInput3.Text in OnInventory)),
       Notify("errormessage"), Error),
      )
   );
Reset(TextInput3)

Best regards,