How to Allow Only One (1) Record in a Particular Status at a Time?

[Becca37]

I have tables called Clients and Admissions.  One to many -- each Client can have multiple Admissions. The Admissions table has a status for every record (pending, open, or closed) but they are allowed to have only have one (1) unclosed (so 1 Pending OR 1 Open) admission at any given time.

It would be easy peasy in a straight-up SQL stored procedure -- just query the count of open and pending records before insert and if either one already exists return a message saying as much and don't insert the new record. 

How do I the equivalent of that in the CASPIO world?

[Hastur]

@Becca37 Hello!

It is possible to implement such a workflow using Calculated field and additional JS code.
The idea is to count entries with needed statuses within the table using Calculated Field.
If there are more then 0 entries - we need to hide submission form and show the message.

I created an APP for you. Please feel free to import it - JS_Dynamic_Disable_Submission_1_0_2019-Apr-09_1223.zip
Let me know if you have any questions.

Client credentials are:
Login - Password
client1 - 1
client2 - 2
client3 - 3

[Becca37]

On 4/9/2019 at 6:25 AM, Andrew said:

@Becca37 Hello!

It is possible to implement such a workflow using Calculated field and additional JS code.
The idea is to count entries with needed statuses within the table using Calculated Field.
If there are more then 0 entries - we need to hide submission form and show the message.

I created an APP for you. Please feel free to import it - JS_Dynamic_Disable_Submission_1_0_2019-Apr-09_1223.zip
Let me know if you have any questions.

Client credentials are:
Login - Password
client1 - 1
client2 - 2
client3 - 3

Thanks! I'll have to take a look at this later today. :0)

[Becca37]

I loaded the file but it errored on the datapage import. That's OK though, it gives me the "how the heck do I do that" part information I needed. Thanks much!

Do you (or anyone) know if there's an easy way to test SQL that we can put into virtual fields? Right now I'm writing it in the DataPage's virtual element's formula input, "finishing" the DataPage, going to my web interface, loading the page, seeing that the results are not what I expect, returning to Caspio, opening the DataPage, navigating to the virtual element, tweaking the SQL, "finishing" the DataPage, going to my web interface, loading the page, seeing that the results are not what I expect, returning to Caspio, opening the DataPage, navigating to the virtual element, tweaking the SQL, .... well, you get the idea.

It would be great if there's a tool within Caspio that just lets me input SQL and run the raw query, adjusting as needed until I get the desired results, than I can go put that useful SQL into the DataPage and finish. And be done. Is there one and I'm just not seeing it? :0)

[Hastur]

@Becca37

You may use the "Preview" button to test SQL queries. The "Preview" button opens the datapage in new tab with current set up before you finish the adjustments.
The article regarding this feature - https://howto.caspio.com/datapages/managing-datapages/previewing-datapages/