How to prevent records being created if a condition fails?

[redesign]

I have been tasked with creating a job request app, and I keep hitting obstacles - spreadsheets are not my thing so I am out of my depth. I have a working prototype that does the job, but the client wants records to be created only if their sales reps are found in the database.

So I have tried to use the "Only if this condition is true" in the Add action for the relevant table - problem is, the app creates records every time. It does not appear to validate the condition. What am I missing?

The app has three views - an Intro/Onboarding view, a New Install view, and the Job Completed view.

In the New Install, we want a rep to enter their code, which fetches data from the Retailers database. They then fill out the form and submit the job - we want the job created, only if their details were found (valid retailer code). If the retailer code returns no data (invalid retailer code) how do I stop the job from being created?

Here is the conditional expression I am using for the Add action of the Clients table (the table with the job request details):

ISNOTBLANK(
  FILTER(
   "Find Retailer",
    IN([_THISROW].[Find Retailer Code], LIST([Found Retailer Name]))
    )
  - LIST([_THISROW])
)

Link to the app prototype here:
https://www.appsheet.com/start/f3f5571c-2e29-4b9f-a88a-1e5eb3ee2151

Some valid retailer codes: david, bob123, koalahvac 

Nice to have: Ideally, we would like to present a screen asking for the retailer code before the New Install view, and if the code doesn't exist, prevent the user from getting to the New Install form (show a No Access view), and if the code does exist, then take the rep to the New Install view with their data pre-filled.

At the very least: Prevent a job request being created if the retailer code doesn't exist in the Retailers table.

[DPM_reports]

What you can do create a ref list which shows a drop down of the retailers code........and make the retailers code table view only.....

Hence when the user has to enter the code.....the only option he has is to select a valid code

[redesign]

Thank you for the suggestion DPM_reports, however my client has a problem with this, in that they don't want reps to be able to see other reps. This has made life difficult - and why we want to at least prevent a job being logged if the retailer code does not exist (i.e. not found).

I also tried to prevent the Add event if three other fields were empty (Retailer Name, Retailer Mobile and Retailer Email), but this doesn't seem to work either, and a record is created every single time. I do not  understand why this is so - is it an AppSheet bug? Are fields with formulas in them not considered empty? 

[DPM_reports]

In that case use a valid if expression like Retailercodetablename[column]

here column must be the the one where retailer codes are present.........if the entered code is not present in the given column......entry wont be permitted

[Joseph_Seddik]

Welcome to the community!

It is always better to post screenshots of your relevant tables and their columns to get better answers. 

Assuming that "Found Retailer Name" is the column in "Find Retailer" table, then your expression should be:

IN([Find Retailer Code], Find Retailer[Found Retailer Name])

Ideally, your tables should have correct references among each other, so that you are able to use Valid if and Suggested values fields to achieve both of your objectives. 

Also, instead of a separate form, you can control the display of other columns in your New Install form so that they get displayed only if a valid Retailer Code exists in the Retailers table. 

Please read these guides:

References Between Tables | AppSheet Help Center 

Valid_If Column Constraint | AppSheet Help Center 

Dependent Dropdown | AppSheet Help Center 

Dropdown from Valid_If | AppSheet Help Center 

[redesign]

Thanks for your reply Joseph! I have gone through the AppSheet help documents, including those you linked above, multiple times and I'm afraid they just go right over my head. The terms and syntax may be explained in them, but the examples given are less than useful and I just cannot grok how to apply them to my case. So much for "no code" applications hey. 🙂 My background is in visual design (web/HTML/CSS), so SQL queries and so on are not really in my wheelhouse. I've been dabbling with AppSheets for about a week so far, and like what I see, but my skills are hampered by a lack of knowledge in this space.

I've included some screenshots of my tables below:

Clients (details about the Install Job Request)

Retailers (details of the sales reps)

Find Retailer (intermediary table for looking up the Retailer codes and Sales Rep details)

I'll give your expression a try and see how I go. To keep things simple, I was really hoping to just prevent an Add event if the Retailer Code was not found in the Retailers[Retail Code] column, but cannot work out how to achieve this.

[Joseph_Seddik]

What you're doing is way more complicated than AppSheet. 

I don't think you've read these documents at least as carefully as you should have; for I don't see a single reference in any of your tables.

Anyway, there's no problem, let's take it step by step. First please read this guide carefully and tell me if you have difficulties:

Data: The Essentials | AppSheet Help Center 

[redesign]

Thanks Joseph, have read the article in its entirety, I already have a basic understanding of the concepts described. But before I rebuild my datasets from the ground up, what I am failing to understand is why a record in the Clients table is created every time, regardless of whether the condition is true or false - my expression is no doubt wrong.

On the New Install view, a rep will enter a Retail Code - if found, the Retailer Name, Mobile and Email fields are filled with the corresponding data - ergo, these files are not blank, thus when Save is pressed, create a record for the job in the Clients table.

If the Retailer Code is not found, surely Retailer Name, Mobile and Email form fields are blank, thus a record should not be created:

Furthermore, despite having "Needs confirmation" turned on, I never get any alert requesting confirmation of the Add behaviour.

I didn't think that achieving this would be particularly complicated - if the Retailer Name is not found due to an invalid Retailer Code not being matched in the Retailers table, then don't Add a new record in the Clients table. Why can't this be done? What expression would result in the condition being false?

[Joseph_Seddik]

I fully understand what you are trying to do, and for this I’m telling you that you should build references among your tables, and it will be just a matter of correctly setting the type of relevant columns, not “rebuilding the database from the ground up”.

Once you have this, it will be easy to achieve the desired behavior through your columns’ Valid if fields. You’ve misunderstood how the behavior action works, so your expression makes no sense. 

We said we’d take it step by step. Now you’ve read the first part, please read this one. Thanks. 

https://help.appsheet.com/en/articles/961426-references-between-tables 

[redesign]

Thanks Joseph, yeah I'm coming at this from a web dev/JavaScript perspective, so my approach is not quite right. I used to dabble with MS Access back in the day, so it's been a while! 🙂

Have read through the references article, starting to understand how my data could be better structured.