How to Run a workflow if the Value user entered exist within the database?

Questions
1

So, I’ve got a table with two columns

  • Column “Coupon”
  • Column “Status”

The table has 9,999 rows with unique values in the “coupon” column. The “Status” column is empty by default.

I’ve got a form where the user has to enter this email, password, and coupon code.

I want to run the following workflow:

If the code is correct that is it exists within the list of those 9,999 coupons and the value of the “Status” column is empty.

  1. I want to sign the user up and
  2. Set the value of the Column “Status” within this table to “Active”.

If the code the user entered is wrong that is it doesn’t exist within that 9,999 row list, I want to display/toggle the Popup that has the text saying “The coupon you entered is invalid”.

Also,

If the code the user entered is correct, that is, it does exist within that 9,999 row list, but the value of the “Status” column is already set to “active”. I want to display/toggle another popup Popup that has the text saying “The coupon has already been used”.

Can you guys suggest a way I can make this workflow?

Thanks

2

You have to run a simple search on your table of coupons. The search constraints would be ‘Code = Inputted text’ & ‘Active is empty’. If the search:count > 0, then the inputted coupon exists and you can apply the discount + set Active to ‘no’ (or even better ‘current data time’ so you know when it was used). If the search returns nothing, then the coupon entered is not valid.

There is a consideration which is way too often overlooked; the coupon datatype has to be private, and it cannot be searched for. Otherwise a tech-savvy (not particularly savvy) user would just be able to query the coupon codes from your servers and use them freely.

Set your coupon codes to completely private and unsearchable. Then create a backend workflow, check the checbox that says ‘this workflow ignores privacy rules’ + run the search in there and use the workflow block ‘Return data from APi’ and just return a 1 if the search was succesful, and a 0 if it wasnt.
Then connect the API Worfklow to your API connector.
I explain how to set this API call up in the API connector in the following post: How to trigger a backend workflow? - #3 by nico.dicagno

Once set up, just call the API worklow from the page, and if it returns a 1, apply the coupon. If it returns a 2 do not.
Two options for returning further data about the coupon (discount percentage, fixed discount/ applicable shops / minimum spend to apply).

  1. Return the Unique ID of the coupon + on privacy rules, set all fields you need to visible.
    This might not be well known but Searching a Thing by Unique ID will return the Thing desipite privacy rules not being met
    This can be used to your advantage. If you return the Unique id, you can fetch all the fields which are visible by default by everyone else.
    Not an amazing solution, because you have to run another search after API response.

  2. Return all the Coupon data you need from the API response directly, and use custom states to save the data to the page. This is probably the better solution.

3

where do I have to do this search for?

4

In a backend API, as described above.

5

I’m doing a little test where I do search for Table coupon = User input coupon

#1: If count is > 0, show text “Match found”.
#2: If count is is 0, show text “Match not found”.

It’s always giving the #2 even when it is supposed to give #1.

Am I doing it wrong?

6

Have you checked your privacy rules? Is the Coupon text field visible to users? Is the ‘find this in searches’ checked?

7

It was disabled for everyone else… Just enabled it.

Now I’m getting both #1.

Thanks Nico!

8

Happy it worked. However I would recommend setting the privacy rules and following the steps I mentioned above.

Now that you enabled it, people are able to find coupon codes directly from your server and use them. Are you ok with that?

9

I’m keeping the coupon codes in a separate table, does that help?

10

Nope. Unless that table is protected by privacy rules, it can be accessed too.