You question is not an easy one to answer. In short anywhere from 1 to 1000 additions, changes or deletes in a day. I am happy to provide more details for you & the team to help you understand.
It is part of a rather complicated / interlinked, Tracking number generator, Calendar, and timesheet system (and other apps). This formula is being used in the Calendar system (table based not google calendar). Which in itself (on the surface) may only have 10 to 50 additions / changes / deletes per day to the Calendar.
Here’s a very very basic breakdown of the system (I will try to be concise).
Tracking numbers (New entries per day 1 - 200) This is the systems core, everything uses this.
- You input the job data and it creates a sequential number
- You can bundle multiple tracking numbers under a master tracking number (important later on)
~RELATED / SUPPORT SYSTEM TABLES~
- Customer - Customer data
- Customer assets - Customer equipment data
- Corrective log - Technicians log equipment errors, tied back to the jobs Tracking number
- Estimate Log - An estimate bundle (with a new related estimate tracking number) for each
corrective batch
- Existing corrective & estimate key id’s can be turned in to new job tracking numbers. a corrective key id (or a full batch of correctives, multiple key id’s) can get a new tracking number and become estimates. Alternately individual estimate or corrective key id’s can be “flipped” into tracking numbers to become jobs. For example 20 corrective items (20 key ids) can become a single tracking number which gets estimated and sent. Of those 20 estimate items a customer can approve any combination of all or some of the items. Each approved key id can then be flipped and become a new job tracking number. Say they approve 17 items, there would then be 17 new tracking numbers, but there would also be a 18th MASTER number (used to issue a united invoice). all 18 numbers are interlinked in the system (again this is important later).
Time Log (New entries 10 - 100 per day)
- Allows the tech to fill out a timesheet for the tracking number or numbers they were assigned
- Part of this data is identifying if the job has been completed.
Calendar (New entries 10 - 50 per day)
- Table based, not at all driven by google calendar.
- The Calendar is split in 2 apps, a management and scheduling side, and a read only technician display. This has been done to keep the data low and the speed of the mobile technician calendar as fast as possible.
- Allows management to pull up Tracking numbers and all of the related data (Customer, correctives, estimates) and populate the calendar event with the related data (Notes, asset data, corrective, or estimate repair information, parts purchases and so on).
- In the case of individual assignment, single day & stand alone tracking number projects, when a job is complete we do not need to have any concern of work being duplicated.
- In the case of Master tracking numbers, which typically involve multiple technicians over the span of multiple days. Using the example of the 17 approved jobs under 1 master number above, We create a new calendar entry using the master number. We hide the master tracking number and the technicians on site read the notes section to see what tasks need to be completed and what the child tracking number for that task is. The technician notes the tracking number as being completed when using the time log app. The Calendar notes, that detail the list of the 17 tracking numbers, automatically updates and removes the data for the completed job from that master list (the notes in the calendar). Keep in mind, a master tracking number job that has 17 parts, might have 3 technicians working on it for a week (maybe more). That’s 15 or more daily calendar entries to keep current notes / statuses on (255 total notes).
The system is rather large (12 apps working together as one). The technician Calendar app references only the Calendar table as read only to keep the speeds up. So the data cannot be referenced via Virtual Columns on the technician app. It can be referenced in the managers calendar app, but then needs to be written to a physical column so it will appear in the technician app. As soon as you write data in Appsheet you need to update each individual instance of the data to update if the virtual data changes so it writes correctly so the technician calendar displays correctly. I know there are ways to do this but they all involve someone doing something in the app. The google sheet side arrayformula takes care of this with out the need to update anything. We then always know that the calendar data across all of the technicians calendar entries is up to date.
I use many google sheet side arrayformulas across all of the apps to facilitate automatic updates like the calendar note update. For example the Correctives table has one that looks at the Estimates and the Time Logs to see if estimates have been created or if the corrective has been done (estimates has a similar formula to see if a project has been completed). I also use an arrayformula to create (problem free) sequential tracking numbers, as Key ID’s are confusing for our technicians. Key ID’s are still there, the technicians just don’t use them to communicate. It’s simple to say that you are working on Job 3520, and search that number over the entire system, or remember it to enter it into your time log, rather than 6029e56b.
I hope I have given enough detail, but not too much to be confusing.
LinkedIn
Twitter
