Compound Form Using Actions

I am building an app for facility managers to request service tickets from our company and for our company to track requests through invoicing. Currently most of the communication is handled via text messages, emails and/or phone calls.

Design Intent
Users will only see a list of facilities they manage. To initiate the request they tap a facility and enter service request info. They won’t have to worry about entering their contact info, facility address, etc. I also need an auto-generated sequential number to be added to the ‘ticket’.

I am trying to create an action to accomplish this but I don’t know how to navigate to other sheets or tables to bring elements together to be added to my ‘ticket’ row.

I will later tackle a way for technicians in the field to select materials from the app and add them to a ticket and also add hours for labor costs. Also will have way for facility managers to see status of their request. Then have office generate an invoice from completed work based on ‘ticket’ sheet.

You can do this via an arrayformula in the Sheet

=ARRAYFORMULA(IF(A2:A="",,ROW(A2:A)-1)

About the customer data, I guess you can just take the email over to the submissions and all other things can be done via a relation - lookup. Would it work?

@ThinhDinh Thank you, still working on the fundamentals of how to break-out table across tables, e.g. have one wide table with ‘all data’ or have many tables and then ‘join via relationships’, I think I got it good enough for now.

However, I need some help on determining whether or not it is even possible to auto-generate a unique ‘ticket number’ for a service.

For each unique ‘creation date + facility + problem desc’ I want to a sequential 5-digit number number to be generated and added to the row.

For example, here are (4) submissions. I want the ticket number series to add the next value in the empty cell ‘30004’.

The value is a ‘header’ value, meaning that this value ‘30004’, would be used by the worker in the field to track materials used for the work and also hours spent. This same value will be used to generate an invoice for the total cost and then sent to customer as an invoice for payment.

Is the easiest way to have a separate table to track existing values? I mean concatenate facility info that makes a unique row and then for new entries check that table for last value and then increment by 1 and then add that value back to the table with the concat string?

For example:
value,faciltiy_info
30001,GlideLaunching LoftJack Vaughan3rd Floor Gym ShowersNo hot water
30002,GlideLaunching LoftJack Vaughan2nd Floor KitchenLeak
30003,GlideLaunching LoftJack VaughanRoofReplace hose bibb

So the automatical incremental value is enough for you then, and you can keep your current setup?

May I ask why it starts at 30001? My formula above will work, it starts at 1.

@ThinhDinh
It will work to generate sequential number, sorry I am not explaining it correctly (I think). The arrayformula will work. Starting with ''30000" is just number series that would keep new tickets identifiable and not confused with current process for ticketing.

What will happen is, request submitted, number assigned. Then this number will be used to add additional entry rows;
ticket_no,material_1qty_price,labor_hrs
ticket_no,material_2qty_price,labor_hrs
ticket_no,material_3qty_price,labor_hrs
ticket_no,material_4qty_price,labor_hrs
ticket_no,material_5qty_price,labor_hrs
ticket_no,sum_material_prices+sum_labor_hrs

I envisioned adding all subsequent entries on the same table…but perhaps I need to rethink this and just use this table to gather the headed info,like the ticket number…then each row would be a unique ticket number and the soluton works perfect.

So then I’d create a new table that takes the record, with assigned ticket number, and then materials and labor hours are added to that table

1 Like

Just to chime in on the automatic assignment of ticket numbers. The only problem I see with @ThinhDinh’s solution is that there is potential for the ticket numbers to get misaligned if you delete a row for any reason. What I would do is maybe create a single value column either in the sheet with the form button that creates a ticket, or in the user profile sheet that gets the max ticket number from the tickets sheet. Then create a math column that adds 1 to that single value column and that would be the number of the next available ticket number that you pass through the form to the ticket sheet.

You just may need a seed row of 29999 to get your new ticket numbers to start at 30000.

I always get nervous when dynamically assigning values to a row where the row position of the rest of the columns could possibly change. It may work now, but then later you realize that you have 1000+ tickets that had the number change because you deleted a row and you’re trying to scramble to correct it.

As for subsequent entries, I agree that you should have a master ticket table with only unique ticket rows. Any other data that takes up multiple rows should be in a new table and have the ticket number assigned from the master ticket table.

1 Like

@Jeff_Hager @ThinhDinh thank you both, I will digest, dig in…and resurface I am sure.

1 Like

@Jeff_Hager I think I’ve configured correctly yet oddly only every other form submittal picks-up the next available ticket number. I submitted the forms in quick succession with similar intervals between submits and its consistently skipping a value. Any thoughts?

30003 is value I manually entered and starting with 30004 the values have been added via form submit.

Can you show how you set up the columns? Which sheet did you ultimately use to set it up?

@ThinhDinh Happily. I added a Single Value Colum to the Users sheet & a Math column next to it. See below. In checking into it just now I see that what is happening is that upon every submit the ‘Max Ticket Number’ alternates [cycle_1 and cycle_2] between ‘blank’ and actual value. Maybe I’ve configured the Single Value column incorrectly.

Ok, couple of things.

  • Is your ticket number in the ticket sheet set up as a numeric or text column type?
  • Is there any reason you are using 2 positions from the end? ‘Last’ would be better than ‘From End’
    image
    In hindsight though, a Rollup column would be better instead of the Single Value column to determine the Max, but the ticket number needs to be a numeric.

right on!, ticket column was/is a number value
nope, just wrong, I see now how that make it look at previous empty cell
changed to roll-up and moving on, many thanks again

2 Likes