I can use some guidance!
I have a simple UX that creates an order. Of course, there's a repeating section for each item ordered. The UX calculates the Total Amount, Sales Tax, Sub Total, etc. When the user saves the order, it is submitted to a SQL Table. Sounds easy enough, but...
The way I have it set (which I know is wrong) is that the SQL table has a "JobNum" field that auto-increments when the job is saved (works perfectly). HOWEVER, since the Job # doesn't get its value until the Submit button is clicked, the "detail" fields in the repeating section don't have a Job #... and, therefore, they can't be linked to the main Job table in SQL Server. I hope that makes sense.
To make it easy to visualize, here's picture of the UX, and the repeating section:
Repeating.jpg
I don't know if I should, somehow, set the Job # on the client side, before Submitting the Job to the SQL Server... If I do that, I am worried that when multiple jobs are entered at once (during the "busy season", there can be as many as 8 people entering simultaneously).
Then, I came up with the brainstorm to "Create the Job" when the UX is opened (I used an "INSERT" command in an Ajax Callback. Worked perfectly), which created a unique Job #. BUT, then I didn't know how to capture that NEW Job # to bring it back to JobNum field on the UX. AND, of course, then I have to make sure that Job # is part of every row in the Repeating Section.
Here are the SQL Commands I used (they work... I just don't know how to get the value from the SELECT command to populate the UX):
Dim sqlCommand as c
Dim sqlCommand2 as c
Dim SetDate as c
SetDate = Date()
flag = cn.open("::Name::RocBaugh")
if flag = .t.
args.set("SetDate", SetDate)
sqlCommand = "INSERT INTO tblJobInfo (DateOrdered) Values (:SetDate);" 'creates a new record, which issues the next "JobNum"
sqlCommand2 = "SELECT GetJob FROM qryGetJobNum;" 'get the value just created from a SQL "view" that shows the Maximum value for "JobNum"
flag = cn.execute(sqlCommand2)
end if
I'm sure this is a common scenario. but I'm having a mental block about the right/best way to get this done.
I'd appreciate any guidance or suggestions you might have.
I have a simple UX that creates an order. Of course, there's a repeating section for each item ordered. The UX calculates the Total Amount, Sales Tax, Sub Total, etc. When the user saves the order, it is submitted to a SQL Table. Sounds easy enough, but...
The way I have it set (which I know is wrong) is that the SQL table has a "JobNum" field that auto-increments when the job is saved (works perfectly). HOWEVER, since the Job # doesn't get its value until the Submit button is clicked, the "detail" fields in the repeating section don't have a Job #... and, therefore, they can't be linked to the main Job table in SQL Server. I hope that makes sense.
To make it easy to visualize, here's picture of the UX, and the repeating section:
Repeating.jpg
I don't know if I should, somehow, set the Job # on the client side, before Submitting the Job to the SQL Server... If I do that, I am worried that when multiple jobs are entered at once (during the "busy season", there can be as many as 8 people entering simultaneously).
Then, I came up with the brainstorm to "Create the Job" when the UX is opened (I used an "INSERT" command in an Ajax Callback. Worked perfectly), which created a unique Job #. BUT, then I didn't know how to capture that NEW Job # to bring it back to JobNum field on the UX. AND, of course, then I have to make sure that Job # is part of every row in the Repeating Section.
Here are the SQL Commands I used (they work... I just don't know how to get the value from the SELECT command to populate the UX):
Dim sqlCommand as c
Dim sqlCommand2 as c
Dim SetDate as c
SetDate = Date()
flag = cn.open("::Name::RocBaugh")
if flag = .t.
args.set("SetDate", SetDate)
sqlCommand = "INSERT INTO tblJobInfo (DateOrdered) Values (:SetDate);" 'creates a new record, which issues the next "JobNum"
sqlCommand2 = "SELECT GetJob FROM qryGetJobNum;" 'get the value just created from a SQL "view" that shows the Maximum value for "JobNum"
flag = cn.execute(sqlCommand2)
end if
I'm sure this is a common scenario. but I'm having a mental block about the right/best way to get this done.
I'd appreciate any guidance or suggestions you might have.
Comment