Build a Dispatch App with AppSheet and Google Sheets

all right we should be live now welcome everybody to another uh exciting webinar so glad that you guys could be here today uh my name is clark james i'm joined with none other than stephan cormont president ceo founder of crew technologies as well as appsheettraining.com so glad you guys could be here if you haven't checked out our app sheet toolbox chrome extension please check that out we have um done some descriptions we have done some tutorials on how that works and how you can get that uploaded onto your browser it is so helpful we did change the name from crew tools to app sheet toolbox we thought that sounded a little bit uh better so hopefully you guys like that uh but without further ado we're gonna go ahead and go on in to today's demo we're gonna be building a dispatch app uh stefan worked pretty hard on this uh last week uh sorry that we did not get to show you guys last week but we're so glad that we're able to do it this week stefan go ahead take it away all right thank you clark yeah one of the top use cases for app sheet actually is dispatching and field service organization and you know app sheet actually has a ton of really useful features for uh doing those kind of workflows so i figured this would be a fun uh fun demo for us today clark can you hit the next slide my man okay some things that i want to hopefully cover in this session is talk a little bit about relational database tables uh so two data objects that are related to each other uh you know maybe like a an employee who will go on many assignments that's a example of a relational database concept we're going to look at geotagging and mapping we're going to look at interactive dashboards and seeing how we can see multiple screens of data at the same time on one view in app sheet talk about some dispatching workflows so how to actually synchronize your operations based on events that happen in the app and then maybe we'll talk about bulk dispatching if we have time at the end so again i love questions if you have tried dispatching before running any issues or you have any questions along the way about anything that i'm showing please uh shoot something in the comments we're watching the youtube comments um and would love to field your questions so i'm gonna go ahead and share my screen should be good now all right okay so this is a little bit about the app that we're going to be looking at today so we have a view of our team so this is uh my company i i'm right here uh boss we'll say uh nick fury and i have a team of heroes who serve a variety of clients so this is a list of our clients we have the white house we have the eiffel tower buckingham palace uh sokovia the daily planet all sorts of fun places that occasionally need some assistance and then we have an hq dashboard where we can see any events that are in progress and who is working those uh events and then we can go in and assign a new event all right so that is a little bit of what we're going to be getting into today let's start off with our data so every good app sheet app is going to start off with an understanding of what are the data elements that we're interacting with so for a dispatching operation you're usually going to have clients that you serve you're going to have employees contractors or team members that are fulfilling service for clients and then you have the service record itself so something to track when where who how and what is being performed by a team member for a given client so we have those things modeled out here i have a list of team members and we can see i've got emails name phone number i'm actually going to bump this in a little bit here so we can see it i don't have an address for anybody but you know you can just add that field if you wanted to i have a title i'm actually grouping my team members by classes so sometimes you have subdivisions or uh you know different team members of different locations or groups so this is kind of an example of how you can classify uh a subgroup of your team and then i have just normal urls for images and i have some rates here so we can actually calculate pay based on time spent on a job now if you're wondering i don't have a unique id column if you're a little bit versed in app sheet usually app sheet asks you hey do you have a column that is some sort of unique identifier for your table i don't have a special one but for employee tables or user tables i'd like to use um i like to use the email column as the unique id this makes a couple of things easier in app sheet so i like to do that for specifically those employer user tables now you can see i do have a proper randomized id column or key column in my client table that's because i'm not trying to classify my clients by their email address in particular so i have name for them i have an address again because we want to see those mapped out in particular uh phone email website not using those fields right now but i do have a column here called team assigned and this is a list or this is a specific team member that would regularly service this customer so if we want to create sort of standard routes or something like that this is a good start in that direction and then maybe we also have a a fee schedule so i'm going to add a fee column and let's say i'm going to charge each of these customers a hundred dollars per hour per service performed now that's not your typical hero work heroes work for free right but not in this universe so and since i'm not using these columns i'm actually just going to go ahead and get rid of them and now we have our job table so this is the work that's performed for a customer by one of our team i have a randomized id this will be the primary key and actually i did want to mark in the client table that this team assigned column is actually going to be a reference column to our team table and so i like to mark foreign keys that's the the database concept name for the primary key of one table showing up in another table it's the key column in table a but it's appearing in table b that makes it a foreign key so it's 4 in table b so this and actually this is going to be set as a reference type column now for our job i want to mark when it happened so i have date and time columns separated sometimes you can put those together if you like we have our team id here again this is going to be a foreign key so i'm going to mark it blue just so it's clear for everybody to understand and i'm pulling in some data from my team member here so i'm grabbing their name their phone their class and their title and then if it's a normal route or it's a one-time service so normal route is used to define is this going to be the typical customer that is served by this team member or is this going to be a one-off special service and then we have our client which it would probably be good for me to call that client id since it is a foreign key and then i'm pulling the client name address in in here as well so the reason i'm doing that is because maybe later i want to do some deeper reporting on this table and i need those extra variables or extra values pulled out of these tables if i was trying to keep the table trim and i want to keep less data from appearing here i can assume all of that from the id so i wouldn't have to actually include these columns if i was trying to keep my data set really trim we are going to have a definition of service so what type of service is going to be performed here and maybe it's two types of services so i actually have a comma separated list here we'll model that with an enum list column then we have a progress or a status type column so it what's the status of this job is it not started is it in progress is it finished are there issues there are different types of statuses that you can give and so it's common for these types of it's like service line item tables to have some sort of status column and then maybe there's some time tracking and some gps tracking as well so you want to know when they started when they finished and you know we probably want to add a total time and then a team pay and client cost so how much will my team member get paid and how much will my client pay based on the total time between these two fields and then we have our classic notes column i'm going to bump this up next to the service so they can spell out the service a little more okay now to start this off uh you know i talked about mass or bulk dispatching that's what these uh other two tables are for mass text groups and mass text form we're not going to worry about those just yet so i've got this table here i'm going to bring this into app sheets so i'm going to open up my apps appsheet.com template slash apps and i'm going to create a new app start with my own data and i'm not going to worry about my category just yet i'm going to choose data from google and i'm going to go find this hero dispatch sheet there we go okay app sheet went and read my data and by default it reads the first table in my sheet so it read the job table pulled it in excuse me i'm going to close this one that's my uh my original version i'm gonna shrink this down so it said hey you have a table of jobs i see columns of addresses and so we're seeing map where i see a phone number i see a name and it's starting to you know try to understand what's going on and actually make up some helpful views or suggestions for me now to get my other tables in i'm going to go to the data menu open the tables tab and appsheet is suggesting other tables in my sheet for me to bring in i'm going to accept those suggestions i'm going to add the team table and i'm going to add the client table all right i've got my three main tables in and i'm gonna modify some permissions here so i don't want anybody deleting my team members so i'm gonna turn deletes off and i don't want anybody deleting my clients so i'm turning deletes off there we may need to cancel a job or something like that so i'm going to leave deletes on for this for now next step we're going to go to the columns tab and we are going to see if app she gave good guesses on what each column should be typed as we're going to start in our team table and i'm going to collapse the viewer on the right it saw an email column it set it to type email and it assumed that it was a stable key and so it marked key question mark and marked this column as the key it's going to show it and there's no initial value right now and that's fine because it's an email it should be entered explicitly anyway and then it set the label to the name which i like that's a more human recognizable easily understandable term a guest phone has a text but i'm going to move it to phone all that is going to do is tell aptchy to expect numbers in here and it's going to build some default actions address is set to an address type title is text class is text that's fine actually you know i might want to turn turn class to an enum so if i leave this enum blank what enums do is it what enums do they create drop-down fields for us so let's see if i have i don't have a view of my team yet to visualize this so i'm going to go back into my data and let's look at my classes superhero and action heroes so i've got two options i'm just going to copy those two and i'm going to paste them in my allowed values nope used to kind of auto move the second one there superhero action hero okay and then i want that to be buttons done notes we're gonna move that to a long text photo is an image that's great rate is a number i'm going to move that to a price and that is good for my team table i go to my client table we have an id a client name i'm going to move that to name all name does is tells app sheet hey this is an important and human readable field so i'm usually going to set it to a label and i'm going to make it the default prominent position on any view that you put it in address is as it should be an address i'm going to make a quick save here team assigned so this is set to an email which is understandable because all that's in that field is email addresses but we actually want this to be a connection to our team table so we want to tell app sheet this is more than just an email address this is actually a reference to a row and another table so i change this to type ref and now i actually have to step into the column settings by clicking the pencil change the data type or the data type is ref but i need to go into the type details and i need to change the source table to tell it which table this is from this is from the team table i'm going to press done and then fee to guest right is a price and actually went ahead and put a good unique id expression in here for the initial value of our id column i don't usually like to see those in the app so i'm going to not show that column and i'm going to require the name and i'm going to require the address the team assignment i'm not going to require just yet and then we're going to always require a fee all right save those changes and now let's go to our job our id column i'm going to collapse here so we can see a little more it sets a unique id that's good i don't want to see it though we have date and time set to date and time with good initial values team id again this is set to email but i want it to be a reference and i wanted to reference the team table and i'm not going to worry about name phone class title just yet then type this is going to be type of service this is going to be an enum list so if you remember in my data in my client table or sorry in my job table oh just kidding wrong column services will be an enum list type will be an enum so this is either normal route or one time service normal route one-time service and let's do buttons client id is it text i want this again to be a reference to my client table so going to set it to type ref change the source table to client not going to worry about the address yet not going to worry about uh okay we are going to set the services so i want this to be a multi-select so where they can have a drop down and select more than one option at a time to classify this service so i'm going to set it to enum list and i'm going to click the pencil to go deeper in the properties and i'm going to add some values so what do we have here military campaign hero work and civilian work there are three options that we want to be able to choose from it's our empty space there yep and civilian work okay and just so we can see some different behavior i'm going to make this one a drop down save those changes now we start getting into a few different things here so um notes is long text progress enum again we're going to pick our three favorite values that are already in our data in progress not started and i'm going to reorder these so these little handles here for dragging i'll drag them out like that and we have an extra space there don't want that usually app sheet should trim those anyway but just to be on the safe side i'm going to make that one a button save that change now we have our time in location in timeout location out total time and team pay client cost so these columns are kind of special so by default app should recognize hey this is a time this is a time total time looks like a number i'm actually going to move that to a decimal because i want to track fractions of a minute or fractions of an hour and then team pay and client costs are priced those are good what appshe did is it recognized these data types and put initial values for many of them so we have time now which showed up for my time columns i don't want these to have initial values yet because i want the user to explicitly take action on this row in order to populate these values so i'm going to remove the initial values for now press save and let's see if our spec updated an easy way to kind of understand your table relationships is to go to the info menu open up the spec tab and this doesn't quite refresh as often as uh i think it should so it's not telling us what i want it to tell me anyway we'll get over that by doing this we're going to create views for each of them we have our jobs i want this to be a table for now i'm going to make this the left view and i want to give it a good icon what's a good icon we'll just have car flipping that's very action hero okay we have our job table now i'm going to create a view for our team point that to the team table i want this one to be a card view it's already set there i'm going to quickly customize this i don't want any of these small items here so i don't want to have a small photo and i don't want these header texts so now that i've moved them to none they kind of disappear from the viewer and i'm going to throw the photo in the main image place this is set to name this is set to email that's great that's set to notes text message phone call and it's given me some actions compose email let's say send sms and if i click on this outer bounding box i can set a particular event this one's going to the user details which i think is fine okay that's looking good now i do need a sensible icon here so usually for teams or employees i like to use the user icons let's find a nice one here oh some crowns very very nice there okay and then let's create a view for our clients i'm going to point it to my client data table and let's try a deck view here actually we'll go we'll do a different type of card view so the card view is actually a super flexible type of element so there's one called a list element i like this one it kind of shows the address on the left we have our client name and then who is assigned to that i actually like that setup that that's working pretty good if i hit the ellipses right here the little dots um i can customize up to three actions this is view the team assigned view map or go to details um let's let's do view map view team and then none on the third one and then click the outer bounding box here and i want that to open the details great okay so to inspect some of our table relationships we have a a relationship on our client table to our team id that is our team assign column and then we have a similar relationship on our job table so a job is associated to one team member at a time meaning a team member can have multiple clients that they serve and they can perform multiple jobs so you could say there's a one-to-many relationship between a team member and jobs and a one-to-many relationship between team members to clients we can visualize that here let's click on nick fury and see okay because we built these reference columns app sheet did something behind the scenes that we didn't notice if i open my team column settings and i scroll down to the bottom we have two new columns that are blue so the little blue pencil indicates that this is a virtual column a virtual column is a column that doesn't exist in your data but is computed when the app is loaded at any time the app is modified so it's formula driven it's a formula driven inference basically and this one is called related clients and so there's a formula here that is always determining what the value of this column is and it returns a list of every client record that has this team member in the team assigned column and similarly this related jobs column shows me every job row that has this team member assigned as the team id so i can see that nick fury here is normally assigned to the daily planet and that he has performed one related job for the daily planet likewise our client has a one-to-many relationship with jobs so a client can have many jobs that are performed for it so we can go to our client and let's take a look at i don't know flying fortress we see related jobs which is again a column a virtual column that was added and we can see that tony stark did some work here on the flying fortress on february 2nd at 1 35 pm superhero work it was a one-time service and so we can see some of that data this view for my client is actually looking a little drab so if i want to modify this detail view i can click down here in this hot link section to navigate straight to the view controller client detail and i want to set the header column to be the client name and let's see if i can do a card layout view in the detail view i like that let's put the notes here hope there are no notes so that actually pretty much sums up the whole uh whole bit of data for a client nice okay we'll go ahead and save that something i also like to do to make my apps a little more easily navigable by a user and help users understand where they're at in the process of performing their functions in the app is i like to go to the brand tab and i like to show the logo in the header we don't have a custom logo yet but i also like to show the view name and so this helps users understand where they're at in context of other views when you're in a main menu view you can kind of see the view highlighted at the bottom but if you step into let's click daily planet here and then click related jobs from there we don't really know where we're at it just kind of says details right now we can customize that text which we will do um but i like this because it helps the user ultimately understand where they're at in the context of what what data am i even looking at sometimes things can look very similar everything kind of can kind of look the same in app sheet at times so i'm also going to do a more strongly accented footer and let's go dark theme yeah okay so let's customize some of those detail views so that we can better understand where we're at client detail i'm going to go down here in the display name and i'm going to say call this client info and i'm going to go into job detail title that job info and i'll go in the team detail okay so this should tell me a little bit better where i'm at i'm going to go and save it okay now at the top it says team info if i scroll down and i click related clients it tells me client info at the top of the app good that's some helpful context all right now let's talk about assigning these and starting this dispatching process so i showed you a nice dashboard previously with a list of jobs on the left and then a map of jobs on the right i want to recreate that same environment here so i already have a table view called job but i want a map view i'm going to call this job map it's going to point to data for the job table and i'm going to set it to map ux type and i'm going to leave this in the menu side menu for now because i want to combine these two views into one via a dashboard view i'm going to select the map column it'll be the address of service and i can actually plot data from multiple tables at a time here which i'm not going to yet actually you know it might be interesting to see the clients on here as well so we will have the client address and i'm going to turn the location mode off otherwise it's going to show my location as the admin user which i don't really care to see i'm going to give myself a map icon globe that's kind of cool and that'll do it for that job i'm going to move to the menu i'm going to retitle it job list and now for my primary view i'm going to create a dashboard called dispatch i'm gonna set it to a dashboard view type i'm gonna put it in the leftmost position and now it's gonna ask me to select some view entries so i can select any view that i've already created to be part of this dashboard i want the job list to be first and then oops job map to be second and i actually want this to be interactive so that'll allow me to click on a record on the left or a record in the list and then it highlights it for me in the map and vice versa and for dispatching do we have like a radio yeah that's a nice icon and oops clients gotta pick a better icon get a nice city icon there okay let's view this guy full screen because that's where dashboards are meant to be viewed alright so you see it open up the app to this table view which is not where i want i want it to always show up on the dispatch view so i'm going to go into ux and then i'm going to go to the options tab and i'm going to move the starting view to dispatch you can actually introduce a formula here so maybe based on what user is logged in or you know their current gps location or something you might pick a different view name dynamically i'm not going to worry about that yet i'm gonna save that and gonna resync okay so we can see we click on things on the left and it filters the map and shows me a detail view flyout here of some information about that service and then i can add a new service i can maybe assign this to see natasha romanoff but i do need to pre-fill some of these fields this could be a normal route or a one-time service and again i want to set up some logic so that when i click normal route it populates the whatever client this person normally is the assigned team member for and then i want to populate the client's name address in here as well here's our multi-select enum list for services we have our progress buttons i think i want it to always default to not started and i i like that it's leaving time in and location in blank right now total time and all that is zero that's good for now so let's start working on some of that logic all right we're going to go into our column settings and just kind of start at the top and work our way to the bottom of the job form so first thing we want to do is whenever we select a team id we want to pull that team whatever row that correlates to in the team table we want to pull its name column its phone column its class column and its title column so the way we do that is with a dereference formula so app sheet gives us a pretty handy formula if i go into the other section the first one in the list is the reference formula and it has this pattern in the first column reference so anything in square brackets is referencing a column name so in the first column reference we reference whatever column column in this table is the reference column that we want to look through so that's the first one and in this case it's going to be the team id and then we put a dot and you can think of this like okay now we're going through a door at the reference point of team id so like in my app here if i add a new record and i select a team member steve rogers we're going through steve rogers door into his record right here so now after i put that period any column i reference is going to be in the context of this row from the team table so and that actually calls out the lookup column so what column are we looking up in the other table team id and we want to pull back the name and my app sheet toolbox extension is saying hey you have two columns in your app called name one's in the team table ones in the job table i'm gonna grab the one from the team table not that it makes a difference the context is kind of relative to this first column but it's nice to know that i am getting the right column name there so that should i'm going to save that and i'm going to open the app here really fast i'm going to go to dispatch and oops don't have an overlay action button in here yet so i'm going to go to the job list press the new button now if i you notice the name column is hidden so we used to be able to see like this is technically an older version of the app right now so we can see that the name column is here it's hidden now because it's dependent on a value being present in team id so until i select a team id the name column is hidden so now my formula looked through that reference point in nick fury and found his name nick fury switch to steve rogers it switches to steve rogers james bond you get the picture so that looks like it's working well we're going to do the same thing with our phone column so team id dot phone and if this would if it happens to be that the columns are named the same in these tables but i should always uh name i should always be in the second column position in this dereference formula should always be the name of the column as it appears in the table you're looking into so this would be the team table so class title okay let's give that a quick test yeah now we see all those columns are hidden and then now they all populate great now i want it to default to normal route and i want to set the client id to show up as this team member's assigned client which is actually going to be a little more tricky so it might uh might confuse a few people here in the process let's start with setting the default for our type column we're going to set an initial value and we're going to hard code it to the value normal route so our client id is a drop down already because it's of ref column type but i want to restrict the options in there right now it shows me every client in the table but i want to restrict it to only the client or rather i want to initialize it not restrict it you can restrict it by putting a condition here in the valid if that returns a list of appropriate values and if you want to have the initial value set we're going to use our auto compute initial value column so i want the first value if let me use if expression so if our type of service column is equal to normal route then i want to use the team id i'm going to do my d reference formula related clients actually yeah i'm gonna pick any of those so that returns a list of things related clients is a list and so i have to put any the any expression around there to pick one single item from the list and return its value and then if else blank okay great now i'm actually going to copy that because if it's a normal route this person should only be going to clients that are that it's normally assigned to so i actually am going to restrict the uh list of possible clients here as well so i'm going to use this same condition this is a little bit advanced but i think we can handle it so if oops again if the type column is equal to normal route then i want to restrict this list to only this team members related clients now i can do this because this is a list of client ids which is what the client column is looking up right now so it's a reference column so it's always looking at the id the underlying ids of that reference table now if it's not a normal round and it's a one-time service then i want it to be anything and because i'm passing in a list here i have to pass in another valid list as my secondary option in this if expression and so i just want to pull back anything from the clients table so i'm going to do clients i'm going to pick the table name and then id so that will return all ids from the client table and you can see app sheets saying hey you at least spelled everything right [Music] and then if let's give a nice validation error saying this is not a valid client for this type of service nice custom error message so our user doesn't get confused if the validation fails okay i'm gonna i'm gonna try this one full screen so i'm gonna go back over here sync my app and let's start with steve rogers steve rogers doesn't have any related clients so i'm going to pick a different guy nick fury has one related client so the daily planet should be if i select nick fury it should uh show me the daily planet as his client by default and if i open the drop down it should only show me the daily planet as an option if it's normal service let's test that plus button team id nick fury it did set to normal route as our default and it pre-populated the daily planet and the client id and my only option is the daily planet now i have my new button here that's kind of something that is kind of hard to get rid of an app sheet and i'm not going to deal with it in this video but this is working how i want it to be if i switch to one time service it no longer populates a client id with the default value and now i can pick any place now we knew that steve rogers had nothing as his default so if i switch to normal route that should be empty and this should be empty okay good one time service should let me pick anything excellent all right we are working our way down the list now when we pick a client we need to pull its name and address client id client name and address so again you probably guessed it we're going to use that d reference formula instead of looking at the team id we're going to be looking at the client id it's gotta spell it right client id dot is it name nope i think it's just client there we go yep so this is the name of the column and the client table and it's a data type name that's what i want and then address same one client id dot address most excellent okay moving down the list services we've already set that up progress let's set this to default to not started so i'm going to go down here under auto compute set my initial value not started time in location location out those are all fine uh total time so we can start doing some time calculations here i want to do one thing first though i'm going to create two action buttons and a new view so right now we've been viewing this app from the perspective of a dispatcher but i actually want to see this from the perspective of the technician or the service person or my team member as well so i'm going to create a new view and i'm going to call it my jobs it's not really going to be specialized to me at the moment but i actually tell you what i am gonna set it up for me so i have this team member nick fury set to my email address so i'm actually going to do this here we're going to go to data i'm going to create a slice and i only want to see jobs that are assigned to me i call this the my jobs slice this is going to be looking at the job table and here's the condition that my email address so the email address of the current user logged in is equal to this column team id okay so if we look in the data again and job the team id is populated with an email that's a reference to the table and should match my login email and then i want to see everything and have all permissions for now all right now i'm going to go to my ux view and i'm going to point this view instead of to the full jobs table i'm going to point it to my jobs and i'm going to change this to a user icon and it's going to be me a single user with shield i guess the jobs places i'm defending because i'm a hero right okay and yeah let's put this on the left that's fine we'll sort by uh let's group by status or progress and then sort by date created descending time created descending so most recent on top i messed that up date pretty sure i can click things right hopefully and then let's do a little count all right i'm going to save that go over here press cancel resync the app okay now we have a few called my jobs and i see the one job that has been done so far and i'm going to create a new one since that one is finished nick fury this is a normal route going to the daily planet do some civilian work just to do some investigation and it has not started yet okay so very quickly i'm going to throw a little formatting on that progress column so i'm going to create a new format rule called not started maybe do progress not started and this is going to be where the progress column is equal to not started and i want to format the let's format the team id and actually let's format the client id and i can press command click or shift uh control click on mac to highlight another thing and i want to highlight the progress so now client id and progress are going to be highlighted my icon is going to be empty star with a little more solid stuff there and this is going to be yellow and i'm going to copy one and do the second one for in progress and i'm going to do a partially filled star i know it's in here somewhere there it is and this is going to be blue actually let's do let's do orange now we'll do red yellow green that's that's a nice transition i'm gonna copy that i think it's called finished it's called finished progress finished yes and we're gonna do full star not start green colors collapse those save it sync it all right that's looking a lot better i can kind of tell what i'm looking at apart a little bit and now i want action buttons for the not started i want to be able to start time and stop time so i'm going to go to behavior i'm going to create a new action button call it start and i'm going to set the time in to time now and i'm going to set location in to here so here is a latitude longitude function and let's do play button and then i'm going to copy this i'm going to call this stop and for demo purposes i'm going to actually create some variants here with the timeout so i'm going to do time now plus uh 3 hours 30 minutes and pick a better icon oh too many too many squares stop yep okay now let's see how this works so we currently have no values for our start and end time i'm going to press start and there we go we got a time in and a location uh i think it is probably gotten a wrong geolocation that's okay and if i press stop i get timeout with three hours and 30 minutes from now but my location out doesn't seem to be set did i mess that up yes i'm setting the location in here we go okay so let's resync that and i'll press stop and there we go now we got our location out let's add a third function to this so i actually want to update the progress when they click this so progress is going to switch when they press start it's going to become in progress and when they press stop it's going to become finished let's resync that okay we can kind of see the progress here by the uh icon right now it's set to not started if i press start it went yellow and if i press stop it should go green excellent you might want a little uh tighter interaction in a live business app but for this purpose i think this is going to do well so i also want to adjust this job detail view i'm seeing a bunch of stuff that i don't really care to see so i want to see the client at the top and then the next thing i want to see is the uh let's do the team id james bond column order date actually i just want to do time in time out progress should progress up to the top services what do services look like up here military campaign throw notes in there instead okay i think that's nice and tidy all right so let's go back to my jobs i'm going to take this one and i'm going to start it meaning throw it into in progress and now in my dispatch dashboard let's see here dispatch dashboard resync i think i want to split these up this job list i want to group it by progress and then i also want to style these map icons because right now everything looks the same to me so i'll start with the map icons the easiest way to stylize map icons is to have your format rule target the map column so this is the address column in this case so i did a command click there to add the address column to the list of formatted columns all right now we're looking much better we have both colors and icons on our map this is way more visible and legible so remember we are also showing data from our client table here so i think i want to throw a format rule for our client table i'm just going to call it client icon and i'm going to format the address column i'm going to get that same building icon and you know let's let's do like a teal cyan color all right there we go uh i can't see my other stuff anymore don't know why that is hopefully i didn't break anything too bad okay i think it's just covering it up kind of superseding the other icon color interesting let me try this can i reorder that nope well all right we're just going to live with that for now ux view dispatch i'm gonna go to my job list and i want to group that by progress a sending throw a count on there column order let's have the client id first that's i'm going to throw the client name in there so we don't see that icon again and then team id as well there okay and then why not the services okay so that's going to be looking better i want to do one last thing before i adjourn this session with you guys um i want to calculate pay all right so we have on our team data table we have a rate that our team members should be paid for work and then we have on our client table a fee that our client should be charged for work and these are hourly uh fees so and rates so i'm going to first calculate the total time which is going to be the duration uh subtraction of time out minus time in and then i'm going to multiply that by the team members pay and then by the client's fee schedule so it used to be that uh multiplying or getting the decimal value of time in hours is actually pretty tough um between two times you have to do some fancy expression stuff but actually just made it a little bit easier for us so i'm gonna set a formula here for total time and i'm gonna start with the obvious so this is gonna be the subtraction of time out minus time in and what this is going to give us is a duration okay and if i look here in my time things it shows us what a duration data type looks like so it is just a number of seconds minutes and hours but i want to convert this duration into an actual decimal so i'm going to go down here and this actually looks interesting so total minutes this gives me if i pass in a duration it's going to give me the total number of minutes in that duration in the form of a decimal and so i want hours so i can just divide that by 60. so i'm going to do total minutes and then i'm going to divide by 60. and i actually want to do 60.0 just to make sure i'm dividing a decimal by a decimal and i return a decimal and i can actually do a quick test on this if i run test it's going to run that on every column in my table this is the most recent one i did which we knew was about three and a half hours and it's resulting in 3.47 which is pretty reasonable so i like that now i want to do two different types of multiplications okay so i want to get the uh amount of pay i'm going to be paying my team member so i need to do team id dot is it called rate yeah rate times total time and i'm going to do a very similar expression for client cost total uh we'll start with the d reference client id is that right oh i spelled client wrong client id dot fee times total time and i think that should sort us out pretty well just to put a little cherry on top what i'll do in my job list is i will so we have a grouping by progress right well i want we were doing a count aggregation but i want to do a instead i want to do a summation of total client cost so how much revenue is my hero firm going to be making from those finished jobs and i want to add two columns for that so i want to do let's add three we'll do total time team pay client cost okay those are all currently blank right now because we haven't been doing any work on them but if i click here for mr daily planet i'm going to press stop and it calculated our time gave me some team pay and some client cost so let's do the same for our man james bond start finish uh we got bruce banner here in the eiffel tower you don't want the hulk near the eiffel tower just going to say probably not a good idea and it does that calculation as well 4 hours 47 or 4 hours .47 other hours so basically four and a half hours and then we're starting to sum up our total amount of finished work looking at one thousand one hundred sixty one dollars uh now i'm gonna say there's probably gonna be a net business loss for sending the hulk to paris so i don't think i'm gonna make any money today but we have a cool dispatch app so i know we didn't get to get to everything we might uh do a follow-up on this but guys thank you for watching and you can always find more cool content like this on our youtube channel so if you enjoyed this give us a like subscribe drop us a comment uh and if you go to absolutetraining.com we also have some paid courses that go deeper dives into certain types of features or things that help you build really amazing applications all righty clark you got anything you want to share no i should be good hope everybody enjoyed it and we'll see you guys again next week alright have a good one everybody you

Channel: AppSheet Training

Views: 13,425

Keywords: AppSheet, Google Sheets, Custom UX, UX, PDF, Automation, Google Cloud, Email Automation, Document Generation, AppSheetTraining

Id: blxMqyOR7W4

Length: 75min 0sec (4500 seconds)

Published: Thu Feb 11 2021