Collaborate on Invoice Creator Software for Production with Ease Using airSlate SignNow

Invoice creator software for Production

hey everybody welcome back to applifier business i received a comment from sufi in the comment sections in one of my previous videos asking if i could demonstrate how you would create an application that allows you to capture transactions from a customer and then invoice them on a monthly basis this video is going to cover how you can do that and show you how you can create a a system that allows you for easy use to first capture the customer itself select a list of units from a drop down list enter the quantities for that and the associated date of that transaction and save that and then on the monthly basis generate invoices for specific months and for specific customers in your application and automatically send invoices so the customer can receive an invoice email with an attached invoice with relevant information about their invoice and also the associated transactions with that invoice itself so to get started we'll jump into our table as usual and you'll notice that i've set up four different tables for this application first one's transactions this is where we'll customize the different aspects of the transaction such as having a unique id for the transaction who the customer is associated with that transaction the unit that they purchased the quantity that they purchased and then the date that occurred and then for the future parts of this app what invoice that transaction has been associated with and then we have the invoices tab this is where we'll keep track of the different invoices which will have multiple transactions in that invoice and you can see we have different columns to describe that more importantly this has been designed as a monthly invoicing app so we're going to capture the month and the year and the customer and that will be a unique aspect to each invoice and then also the total value and the status of the invoice such as whether that invoice has been pending payment or has been paid then we're going to have a table that describes our customers mainly emails phone number address and then maybe their preferred payment method then lastly we have the units table which very simply is going to house information about the units that can be in a transaction such as the unit name itself the cost with that unit and then an image associated with that unit itself and then i went ahead and pre-populated this units table with some items just so it will help us have some context as we develop this application so let's start with the transaction table and we'll create an app from this table so now that we're in the app sheet editor i'm going to go ahead and add the tables the additional tables all right with the tables added we're going to start first with our main table we're going to be capturing the transactions from the user itself so we're going to jump over to the columns section and expand the transactions table here for the transaction id we're going to verify that we have a unique id being generated for the initial value so when every new record's created there's an identifier associated with that then then for customer we're going to have a reference to the customer's table to store that so we're going to build a reference to the customer for unit we're going to build the unit as a reference to the units table so we can populate the unit selected unit there for quantity that'll be number integer and then date we'll have the date and then for date we also want to make sure that we set an initial value there for today and actually already did that for us so with that transaction set up then we'll move into the next table here and that's going to be the invoices table and this is where we're going to generate the monthly invoices for the user itself so once again we have the invoice id there's a unique id set up for that and then there is a month that has been assigned we don't want this to be a date we want this to be a number because the way we track months in app sheet is on a 1 to 12 basis and then just for added validity additional validity we're going to set a minimum value as one and then a maximum value of 12 to make sure we don't exceed those bounds and then for year we're going to choose a number as well and then we'll set a minimum value of 2000 and then for customer we're going to build that as a reference to the customers table and then total where we want this total value to be a price and then lastly there's status to keep things simple we're just going to use uh enum list called pending payment and paid after that we're going to jump into the customers table make sure that these columns have been characterized ingly so we have email phone number address and payment method which for payment method we can just have as a simple drop-down list here called credit card cash or crypto and then there's also the units table for units table we want the unit to be text and that'll be the key as a unique identifier and then the cost will be the price and the image will be a we will do an image field then i'm going to hit save there's an error that showed up here and that i missed that app sheet that set initial value for these month and year columns and then what we're going to do is replace that initial value not with the date but with the month number and then today so we're capturing the month number from app sheet and then for year we will do the same thing instead of just having today we're going to say year so that way we're extracting the current year from whenever the invoice is being created then i'll hit save so with that we've did a rough characterization of the tables in app sheet and now what we can do is start to test out whatever test out the structure we've we've put together initially so here i have the transaction view that was originally set up and i could go ahead and start to enter the hit the plus button enter a form here and then you notice we don't have a customer yet so we'll go ahead and work through that workflow and then save so now that the customer exists in there we could then add unit to this transaction so say a customer bought a computer case he bought two of them and the dates already pre-populated and then the invoice so right away as we're filling out this form we notice a couple things we don't really need to capture we don't need this transaction id to show up as a field and we don't need the invoice to show up as a field because we're not going to capture this from the user or the input itself we're going gonna you know these are gonna be captured automatically so we can go ahead and remove those so i'm gonna go open up the transactions table here and then under transaction id i'm gonna click the flash next to show and i'm gonna set the context to where view type is not equal to form because i don't want this to show up under a form itself i want this to surely show up in years when i'm viewing the transaction identifier and then for invoice we're going to do the same thing and hide that also in the form view but that's it now i have a transaction where i can easily select the customer and the unit that they purchased along with the quantity and hit save and then we save that record and this kind of leads into the next part of the app development now we start to see how this view looks as we start to build out and add records to the table and we see here that there's not a lot of information so far so i'm going to go ahead and click this transaction link down here to open up the view itself and for the purposes of this app we're not going to make it fancy you can obviously experiment around with the different views available and click that link in the top right corner for more information on how to use views in app sheet but we'll just use a deck view to keep it simple in this app and then for the primary header we're going to choose the unit and then for the secondary header we'll choose quantity be nice if we had a couple other things in this view such as maybe the image of the unit that was purchased and also the total cost of that for that specific line item to do this we're going to pull in some we're going to create some virtual columns in our transactions table to help pull over this data ingly so i'm going to add a new virtual column first one we're going to pull in is the image itself of the item we'll just call this i item image because we already established the reference we can say that the item image is the unit dot image meaning that it's going to go look in the unit table and pull the image field from that table next we're going to add a virtual column here to calculate cost per item and once again we'll use a d reference where we reference the unit table and the cost of that item and then lastly we want to calculate the total cost of that transaction light item so we'll call total cost total cost and then we'll go cost per item dot or times quantity those three created item image cost per item and total cost we'll go ahead and hit save and now that we've added these virtual columns to the app we could then see the image associated with that view that we pulled in and then we can also add other information such as the we could add the total cost here in the summary column you can play around with this cost you know another tweak might be that instead of just quantity five showing generically we might want to go into the transaction table too and add another virtual column here that can be that can communicate more information on that single line other than just the number five so we could just call this item and cost line item and what we can do is concatenate first the quantity of that item we can add on an at sign and then we can add in the cost per item so now in that line we when we go ahead and save it and add that view to the new second line we have a little bit more descriptive meaning five items at 100 we'll make sure we wrap this in text so that we can show the dollar sign so we have this view fairly well set up there's obviously some there's some additional things that we can accomplish here if we wanted to remove these actions for temporarily we can we could just turn that off or if we wanted to identify specific actions to be associated with that we could do that as well but really i would i would advise everyone as you develop these apps try to keep thing as simple as possible first so you don't get bogged down in the weeds so now we have the transactions table set up when we know we have the ability here to select the customers choose the different items available enter the quantity then save that information we notice that we have some additional fields now all these virtual columns that we added we don't want to show those either so we're going to go back into the table and um clean those up too we're going to use the same same function here for the form and we're we're just going to make sure that these are hidden so when any form comes up these aren't showing and i do this method primarily because if i have to create more than one form for the same table i don't have to sit there and manage multiple form settings if i don't need to this is just a very simple way to start out and make sure that you're keeping things simple at the highest level possible in your app structure in this case the highest level that we can control the visibility of our columns is in the column structure itself so now we have the form you can see those those other columns have dropped off and one other feature here too that i notice when i click on users i don't have an image associated with that and maybe that would be helpful for the users itself so i could come here to the units table and click on label for the unit and you'll notice the images now show up when you're selecting those images so then i could continue selecting that information and um enter the appropriate quantity and then hit save so with that we have a method now to capture transactions from the customers now we need to move on to the next piece which is to actually invoice those customers and how we can set up automations to not only create emails and attachments to those emails with the invoice but also track to see when these invoices are have been completed and ended paid so to get started we're first going to create automation and this automation we're going to call generate invoice and this is something we're going to choose create a custom bot this is something you create on a set time schedule so every month on the first you generate a new invoice but for the purposes of this example we're actually going to generate an invoice when a new invoice record is created so for the event we're going to call it when new invoice is created and you see app sheet suggests new invoices record is created so we'll just go ahead and use that because that's simpler in addition to that we're also going to run a process in response to that and this process is going to be called generate invoice email and update transaction data meaning that the goal this process is going to not only send an email with the email attachment to the user we're also going to update our transaction table to ensure that we annotate the invoice record that's associated with each of those transactions that'll be important so we're not double counting transactions on invoices if we don't need to it acts as a step to prevent that from happening oop and i got this in the wrong place for the process we want to call the process that and then we want to first have we're going to have multiple steps in this process so the first step is going to be update transaction data we're going to add another step here and it's going to be generate email and invoice attachment okay with those set as placeholders for the update transaction data table we're going to run a data action and there's data action we're going to create this new action call this update invoice id and transaction table or more specifically we'll call add invoice id in transaction table this is where things might get a little tricky because we actually need two actions that need to run for this automation step the first one is for app sheet to run an action on a set of rows in that we identify what records from the transactions table we then copy the data to so we're going to have to basically pass the invoice id to the transactions table and the first step is obviously going to be to identify the referenced rows those reference rows are going to be all the transactions that meet the criteria for that invoice so if we generate an invoice for february of 2022 for a specific customer then we need to make sure we have a list of all the transactions that match this criteria so we're going to use the select statement to to query this and that we're going to go to the transactions table and we're going to pull a list of the transaction ids for that table where these three values match the first one being where the month of the date identified in the transactions table is equal to this row dot month and that this is pulling from the current record and we're matching those values and we'll do the same for the year then lastly we'll ensure that the customer in the transaction table matches and we'll go ahead and close that up now we have a list of transactions that will be generated from this formula that match the settings i.e the month the year and the customer that has been set up in the in the invoice table so the last part is the reference action that we need to set and this hasn't been created yet so what we can do is jump over to the action table and for this we're going to create an action called input invoice id as an action and that'll be associated with the transaction table specifically because that's what the values we want to update and for the data we're going to set the values of some columns in this row we're going to choose invoice and then for the formula we're going to use the input function this allows us to pass the variables or the values from one table to another so we'll give this an arbitrary name and we'll just call this invoice id and then for the second part there's an initial value set you could just set this to blank because we're not going to allow for any default values to be captured and then we'll hit save on that and then we're going to make sure as always when we create a new action if we know it's part of an automation we'll hide all right so jumping back to our automation itself now for the reference action since we've created that action we'll go ahead and select that action name as a reference action and then we'll go ahead and hit save so bear with us because we have this step that hasn't been fully filled out yet so it's showing an error currently but let's continue now that we've selected this reference action that we created you'll notice that there's another input that shows up now and this is where you can select that input name that we created in the action itself and then we're going to select the id from the invoice field that's been created so now we have this this value of the impose id that we're going to then carry over to the transaction table so we can populate that invoice id in this transaction table here so we'll know exactly what transactions have been logged to specific invoices one thing we want to do with our transaction table is that with our invoice column we want to also set that as a ref to our invoices table and by doing this this will also set up a virtual column so that every invoice has a list of transactions that are associated with it next we also want to create an email with an invoice attachment here so we're going to run a task for this and then we're going to create a new task that's called send email with invoice attachment and then for the table itself we're going to use the invoice table obviously and then for the two this is where you can select the customer that was selected because that represents that's an email address that's associated with that field and then for default content we're going to disable this because we want to set up our own professional looking email and then start to build out the subject here and we could put in some references to the month and year and then for the body i'm just going to copy paste the same thing obviously you can do whatever you want with the body and communicate just know that when you're you're filling out these templates anything in double arrows is inside of double arrows is an app sheet format so in this case very simple i'm just pulling the column value itself from that record but you can go as elaborate as you want as i'll show you in the actual attachment template that will generate next so we're going to scroll down here to attachment content type and we want to generate a pdf for that and then for attachment template we're going to hit the create button this is going to generate a google doc if you're using google docs or a microsoft word doc if you're using other products that aren't google-based products so once that attachment template has been created i can go ahead and click the view now that we open this template in google docs you'll notice that app sheet put together a a template or pdf template automatically for you based off the values in the table itself so this gives you a head start in configuring your invoice to give it your your custom branding that you may need so you could add images if you want to or different colors so it's a invoice from abc corp and then we can you know color this ingly or like i said add images as part of that to get a layout we want just using these as a quick example here but you'll come down here and you'll notice the different fields that have been called out as part of this invoice and this is where you can you know you obviously can customize this however you want the text here without that is not in any arrows are just free text so you can either have these labels or you can you know you don't have to have these labels and i won't belabor this um but i will call your attention to the bottom part and this is where you have a list of your transactions right and this uses that start function where you can call your virtual column here which is your you know related transactions for that invoice and then in that start you have a list of all the transactions that'll be a part of that table and then the way this is set up is this will loop through each transaction in the table and build this table out automatically so you have a list of all your transactions in one place so with our template set up we can go back into the app sheet before i forget we have one last thing we need to do in that for the invoice itself we need to have a total that gets populated to do this we're going to click on total and for auto compute we're going to choose the app formula this app formula will then contain all the related transactions with the total cost of that transaction and then we're going to sum up those items to have a grand total for that invoice one thing we want to do is we also after we add the transaction data we also want to update the status as part of this process to pending payment and this will also serve as a way to trigger the app formula that we mentioned for the total cost to update as well we'll just call it set status to pending payment and you'll see the app sheet recommends that step as well and makes it easy to just select that and add that as a step to your app and we'll go ahead and hit save so the app saved will create a view for the invoice table so with this invoice view i can hit an ad and there's an invoice id i can choose the lump that i'm looking to generate along with the customer that i want to generate those items for and then there's some some items we need to clean up but just to demonstrate the email piece and the automation piece we just set up we can then save this now that it's generated we see the invoice information that we created for the month and year and then for related transactions we see the two transactions that are associated with that month and year ingly then if were to jump into our email box you'll see that we have this invoice that was populated and in the attachment itself we have our attachment here with the invoice information and the transaction information down below now obviously there's a lot of cleanup that needs to be done with all this and i'm not going to belabor that because that's relatively simple at this point in general you can employ those those methods that i showed you initially to clean up one of the final things we can cover in this video is how to clean up this month and year right year has a comma in it that doesn't look right so we can take care of that really quickly under invoices for the number and make sure we uncheck this thousands operator and that went away now for month it's a little bit trickier the ideal solution for creating an invoice when you're generating invoice being able to select the month from a drop down menu in conjunction with the year itself so to do this we're going to add another table to our app called months and this is going to be a very simple table where all we're going to be doing here is have a month number and a month name so we're gonna go just do 1 through 12 and then month name january february and we should be able to just drag this down and populate to december so we can really quickly make this table and then add this to our our application as a read-only table and then in our invoice table under columns we'll make month now as a reference and have sheet looks like it did that automatically for us and in the months table itself we're going to make sure that the name is the label which actually also smartly did that automatically itself so now when we're creating an invoice i now have the months available to choose from instead of numbers and then likewise i can pull that into my invoice as well so that next month i i can clean this up and instead of month alone we could do month dot month name as an example and then include the year um with a space in between and maybe we'll bold that too so now when i create a new invoice the email i get makes more sense with the month and the date so you see here january of 2022. so with that that concludes this video like i said i didn't get down into the weeds on how to customize every little detail of this app the main intent here is to show the concepts around how you would structure an app to deal with invoices i'm going to go ahead and clean this app up after the video and i will include this template in the comment section below under the video so you can use that if you want to as always thanks everyone for watching if you like this video please like and subscribe and also leave any questions in the comments below and i'll get back to you on those thanks everyone for watching and have a good one