Jump to content
  • 0

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


Becca37
 Share

Question

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?

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

@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

Link to comment
Share on other sites

  • 0
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)

Link to comment
Share on other sites

  • 0

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)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...