Increment a field value upon form submission

Knack Community
1

Hello folks,

Rather than using the auto-increment field type, I would like to populate a field value from a field in another table.

My specific example is for a ticketing system I am developing. I have table called [Next Numbers] and a field called Next Ticket.

I have a [Ticket] table, and upon the creation of a new ticket, I would like to grab the value in nextNumbers.next ticket and populate my ticket number field in the Ticket table.

I connecting the Next Numbers table to the Ticket table, then I tried to create a record rule to populate the ticket number field. I could not get anything to work though.

Afterwards I want to increment the next ticket field by 1.

Any ideas would be greatly appreciated.

Thank you,
Tim

2

Hello Tim, if you do not mind, you can contact me via godfredbrown@gmail so I could look at your configuration in the app and provide a solution.

3

Suggest you consider the training courses offered by Fine Mountain Consulting. I refer regularly to their video on inherited data, and many of their other basic and intermediate videos.

4

Thanks Tom. I visited their site, but did not see any related videos. I also tried YouTube but didn’t find much. Are the videos you mentioned part of a subscription?

5

You might want to call Gui and ask about their beginner and intermediate subscriptions. Relatively small cost.

Maybe they are revamping for the newest Knack builder version. Even if, the existing videos are very useful for the basics of Knack relational database concepts.

Their number (720) 432-5852

6

Hi Tim

You can do this by adding a field in the 1 side of 1 to many which is a number field.

Then add an equation field which is the previously set up number field + 1.

Then in in you many side of the connection have a number field.

In your record rules for the form add 2 rules

The first will update the number field (many side) with the connected value of the equation field.

The second rule will update a connected record (your 1 side) number field with the connected value equation field.

This should do what you need. I’m away from computer at moment. But can double check my set up tomorrow.

Craig

7

Hello Craig,

I believed that I followed your advice with the following steps. However,the Record Rules I tried did nothing.
Below are the tables and relevent fields.

TABLE 1
Next Numbers
Field1: Next Ticket / Type Number
Field2: Equation Ticket / Equation {Next Ticket}+1

TABLE 2
Tickets
Field1: Ticket Number / Type Number
Field2: Connection to Next Number (This field can connect many Ticket records with one Next Number record.)

I have a form based on the Ticket table to create new tickets with the following record rule.

RULE #1
Action / Update this record

Values
[Ticket Number] [to a connected value]
Next Numbers > Equation Ticket

RULE #2

Action / Update connected records
Next Number connected to this record (Next Number > Next Number)

Values
[Next Ticket] [to a connected value]
[Next Number > Equation Ticket]

Was this implementation the method you had described?

Thanks,
Tim

8

Hi Tim

Yes that was correct. I have double check my setup this morning and you have the same setup as I do now.

Did your field Next Ticket in this table:

TABLE 1
Next Numbers
Field1: Next Ticket / Type Number
Field2: Equation Ticket / Equation {Next Ticket}+1

Have a number in it i.e. 0?

Craig

9

Craig,

Yes, the numeric value of Next Ticket is currently 1200.
The value in the field Equation Ticket is 1201.

Were you able to make the rule(s) work on your end?

10

Yes. The other thing to check is that connection is field is filled with the relevant connected record.

11

Craig,

I’m not quite sure what you meant by that?

12

Just because your 2 objects are connected doesn’t necessarily mean that the 2 records are connected.

In my set up we keep our running total in an assessment object. If the record we are adding is not connected to the assessment then the record rule won’t run:

image

In the image above record 1335 will not run the record rule but 1334 will run it.

I make sure the connection is in every time by selecting the relevant connection when adding the form:

image

Hope this helps.

Craig

13

Craig,

Yes, that worked. Thanks for much for taking the time to walk me through this.

Sincerely,
Tim

1 Like