General Discussion

Howdy y'all,
Wondering if anyone has come up with a look-up solution in any of their forms.

Essentially what we would like is to be able to have a data source that contains the "customer ID number" for restricted customers only. Users manually enter in Customer ID in the form, and then if it matches anything on that list, we would create a flag in the form. We do not want to maintain a complete list of customer numbers (with a secondary column denoting whether or not it is restricted), because the data for the non-restricted customers is much larger and changes more frequently than that of the restricted customers.  However the list of restricted customer is still too long to manually build out a comparison using conditional logic.

Thinking we could "disguise" another question in the form to filter based on the restricted ID list. For example, if it is a yes/no question that we use, have a data source with each restricted customer ID duplicated for Yes/No as options and then a "No match" yes/no, and then use their selection to pass a value through saying whether or not it is restricted.

Obviously this plan hinges on users accurately inputting the customer ID which is not ideal, but is what we are relying on while we build out a more comprehensive integration.

Any advice or examples of what y'all have implemented in similar cases in the past would be greatly appreciated!

Thanks,
CC

------------------------------
Cecily Stelly
Analyst
Johnson Controls
------------------------------

Hi Cesar,
 SharePoint List as a Data Source would actually be ideal for this so the business owners could update that table as needed. What I can't quite wrap my head around is how to force the form to look up the ID in the data source itself without using the data source as the options for a dropdown question - we cannot have the complete list of restricted customer IDs visible on the form for data security reasons.

If we could filter the Data Destination executions by the data source (or any comparison table) that could absolutely work for us, but it would not a practical for us to try and build and maintain the list of restricted IDs to compare to the input expressions in the filter rules on the data destinations settings.

------------------------------
Cecily Stelly
Analyst
Johnson Controls
------------------------------

Original Message:
Sent: 01-10-2023 16:31
From: cesar mccoy
Subject: Value look-up Based on User Input

Have you tried with a SharePoint list used as Data Source and Data Destination at the same time?

 

Cesar

 

Hi Cecily,

One way you can do this is:

1. Set up a textbox question that your users can interact with ("CustomerID")
2. Set up a dropdown question attached to your data source, with the customer ID as the primary column (this can be a hidden question) ("SelectedCustomerID")
3. Set up another textbox question (again, hidden) ("Restricted")

Your data source for the dropdown would then be a list (sharepoint, etc) of customer IDs with an ID column and a "Restricted" column indicating whether that customer is restricted or not (presumably all would be Yes)

Create conditional logic:

When question "CustomerID" is answered
> Set "SelectedCustomerID" to the value of "CustomerID"

If a user enters a valid (restricted) customer ID in the first textbox, the dropdown will be set to that item, and then the Restricted status will be pushed to the third question, where you can perform actions based on that value - within the form, or as a data destination value, etc.

If users enter an ID that isn't in your data source, the dropdown is set to blank and nothing happens.




------------------------------
Calvin Hunter
Project Manager
Vipond Inc
MB
------------------------------

Hi All,

I think Calvin's solution is a great one! The only thing I might add is, if you know how the Customer ID is structured, you can setup validation to at least confirm that the text entered is in a valid format.

Example: CustomerID = Ryan100001.

In this example, you could validate that the first 4 characters are a string and the remaining are digits.

The validation is added to the text field Properties. Once there, select "Custom" for validation type and then enter the Regular expression and validation text in the fields listed below.

Thanks
Ryan

------------------------------
Ryan Maxwell
Implementation Specialist, Rapid Deployment
ProntoForms
------------------------------

Original Message:
Sent: 01-11-2023 09:33
From: Calvin Hunter
Subject: Value look-up Based on User Input

Hi Cecily,

One way you can do this is:

1. Set up a textbox question that your users can interact with ("CustomerID")
2. Set up a dropdown question attached to your data source, with the customer ID as the primary column (this can be a hidden question) ("SelectedCustomerID")
3. Set up another textbox question (again, hidden) ("Restricted")

Your data source for the dropdown would then be a list (sharepoint, etc) of customer IDs with an ID column and a "Restricted" column indicating whether that customer is restricted or not (presumably all would be Yes)

Create conditional logic:

When question "CustomerID" is answered
> Set "SelectedCustomerID" to the value of "CustomerID"

Set up the "SelectedCustomerID" question to "push" data into the "Restricted" text box, which is the value your "Restricted" column.

If a user enters a valid (restricted) customer ID in the first textbox, the dropdown will be set to that item, and then the Restricted status will be pushed to the third question, where you can perform actions based on that value - within the form, or as a data destination value, etc.

If users enter an ID that isn't in your data source, the dropdown is set to blank and nothing happens.

------------------------------
Calvin Hunter
Project Manager
Vipond Inc
MB
------------------------------

Thanks to everyone for all the great advice!
I was able to implement the solution proposed by @Calvin Hunter and it is working great in the form. I presented to the internal customer yesterday and they were thrilled with the quick turn around and how they will be able to update the data source in the SharePoint site their team was already utilizing.

I'll finalize the form next week and add some data validation as suggested, but ​y'all saved me hours in testing potential solutions myself!

Thanks again - CC

------------------------------
Cecily Stelly
Analyst
Johnson Controls
------------------------------

Original Message:
Sent: 01-11-2023 12:26
From: Ryan Maxwell
Subject: Value look-up Based on User Input

Hi All,

I think Calvin's solution is a great one! The only thing I might add is, if you know how the Customer ID is structured, you can setup validation to at least confirm that the text entered is in a valid format.

Example: CustomerID = Ryan100001.

In this example, you could validate that the first 4 characters are a string and the remaining are digits.

The validation is added to the text field Properties. Once there, select "Custom" for validation type and then enter the Regular expression and validation text in the fields listed below.

Thanks
Ryan

------------------------------
Ryan Maxwell
Implementation Specialist, Rapid Deployment
ProntoForms
------------------------------


Original Message:
Sent: 01-11-2023 09:33
From: Calvin Hunter
Subject: Value look-up Based on User Input

Hi Cecily,

One way you can do this is:

1. Set up a textbox question that your users can interact with ("CustomerID")
2. Set up a dropdown question attached to your data source, with the customer ID as the primary column (this can be a hidden question) ("SelectedCustomerID")
3. Set up another textbox question (again, hidden) ("Restricted")

Your data source for the dropdown would then be a list (sharepoint, etc) of customer IDs with an ID column and a "Restricted" column indicating whether that customer is restricted or not (presumably all would be Yes)

Create conditional logic:

When question "CustomerID" is answered
> Set "SelectedCustomerID" to the value of "CustomerID"

Set up the "SelectedCustomerID" question to "push" data into the "Restricted" text box, which is the value your "Restricted" column.

If a user enters a valid (restricted) customer ID in the first textbox, the dropdown will be set to that item, and then the Restricted status will be pushed to the third question, where you can perform actions based on that value - within the form, or as a data destination value, etc.

If users enter an ID that isn't in your data source, the dropdown is set to blank and nothing happens.

------------------------------
Calvin Hunter
Project Manager
Vipond Inc
MB