Jump to content
  • 0

Pull Number Value From Alphanumeric Text, Put It In New Record Of Same Table




I'm new to Caspio and trying to figure out if/how it might do what I need to create a version of my Access app online. Not using VB code or creating regular queries is a bit confusing to me so I was hoping that if I put a specific scenario up in the forum someone might explain if/how to do it here. This would help me extrapolate to other scenarios in Caspio. 


I have a table for 'Invoices' that uses a field called 'Invoice Display' to show the invoice number. This isn't the unique 'primary key' (InvoiceID) used in relationships and linking. It's just an editable text field for an alphanumeric value that shows/displays the desired invoice number on printouts. 


What I need to do is, when a user creates a new invoice, have it auto-number populate to the next highest number in 'Invoice Display' BUT also be editable to any alphanumeric value the user may want. This is because half my customers want to use the internal auto-number system to come up with invoice numbers but the other half wants to be able to overwrite that number with their own custom alphanumeric numbering system. 


For example, if the highest 'Invoice Display' value is 545 then when the user makes a new invoice it needs to show 546 in the new record's 'Invoice Display' field. But then the user may overwrite that and type in something like 546CA.


On the next invoice creation I need to be able to 'pull' the 546 out and autonumber-populate the new 'Invoice Display' value with 547 (which the user can overwrite if they want). 


This way it accommodates both auto-number and user edited scenarios, sometimes within the same customer account. 


In Access I can use an expression in a query like Invoice Display: (Val(Nz([invoice Display]))) and then do Max +1 to show the next number. Is there a way to do something similar in Caspio, and if so, can someone put the basic steps, as I'm just starting out here? Many thanks in advance!



Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

You could setup a Submission Form that generates the unique ID (InvoiceID), then redirect submitted form to an auto-submit detail page to onload the InvoiceID to the InvoiceDisplay field, then set the auto submit page to redirect to a details page where the InvoiceDisplay field (now displaying InvoiceID) is displayed as text so the user could change if needed. Change the Submit buttom on the initial form to "Continue" so they understand the form is 2-part. The auto submit page will ensure if user abandons the 2nd part of the form that the InvoiceDisplay field value is still captured from the auto-submit form.

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.

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.

  • Create New...