How To Generate a Customize Report and Download in PDF Appsheet

hey guys welcome to my youtube channel in this episode what we're trying to do is to download a customized pdf report in app sheet i have a sample report here i mean a sample application this application is a collection app basically this is a collection date this is collection from january 1st up to may 31st and each collection there is a receipt number an amount and then what is the payment method and then who is the cashier who received the payment so the first i want to achieve or to download a customized report is i want to know how much collection from specific date from the collection date let's say from january 1st to end of february i want to know how much the total collection in this one we cannot extract it right so we will extract the report or x okay we will download it so just to explain to you first to show it to you i have only one currently i have one table in my app editor which is the collection and i have here a database this is my database which is this one in order to achieve to download the report in a specific date you know to calculate so we need to add another another table here which is i name this as all collection so in my old collection i have this column start date end date collection date which is i just copy and paste it here so let's put an initial part value here in start date let's say june january 1 up to 31 of january for example then down here in the collection date column we will up we will put a formula which is uh sort then filter let's filter the collection our database okay and then let's remove this and we're in it's column collection date and this column collection date okay is more done 62 or equals to this start date and this collection date is less done for equals to end it then we can just put like this let's see as you can see it generates the report it's filter all the reports up to end of january which is really that is our goal if you change this let's say i want only for 2nd of january it changed as well so let's so we we are done for this then once you successfully created this all collection like query table you need to add another table which is the collection form this is the form that you want to generate so we have restarted end date i arrange it as per i like so this is how i like my report for example so start date here you put an equal sign and then go to your table it's equals to this it's copy then equal sign and then over here hit enter so it's tally there then how we need to migrate the data here to do that let's add a formula is equals to sword i want i want to use all this sort then query this time you're gonna use very function very old collection so we need only this data so all collection so we want it infinite then it goes like this hit enter so it gives you all the details here so this is from january 2 collection so we wanted to calculate how much the cash the card check and bank transfer also and then the total amount of course so total amount is just the sum of this three so to calculate how much cash so what we're gonna do is we're gonna use a some some if formula some if so this column or if it's let's say if it's cache okay then we need to sum this column all of this until down hit enter so i just want to copy this and just change it this one into card and check and bank runs so since i have four types of payment if you like as well uh i wanted to attach a report here uh that um or we can separate it actually we can also put we can also count how uh how much per agent here so but we don't want to do that this time then as you can see guys if you change this let's say 28 may it change the report into may also then here you can see me and then it's all the way down the report okay so change it to 0 5 10 it changed the report also respectively form also change like this okay then what we're gonna do is we don't want to share this whole database what we're gonna do let's go to our drive so i have a we need to do import range so we're gonna import this this type okay so we'll copy this link so go to the form and go to your drive and open a new google sheet okay so i already opened this and i title it also so in this case we will use the import range so is equals to import port range control b and so what is the table name which is all collection form all collection form what then what is the range a1 to h so a1 a1 2h hit enter okay so this time we wanted to arrange these things so we want to format this as we like okay so i want to format this to look it nice then this one i want to merge for example okay then i'm gonna add some nice label and here for example i want this to put it all in the middle okay then i have it here so basically we have to arrange only okay so i have all here then let's say we want to add a generate time here start date let's say here okay let's say here guys i want to put a date and time generated i want this to just to put like a security features sorry my ipod just i mean my laptop gets stuck okay then is equals to now go back here so i want it to be small letter and here's this one where it's in small letter okay then this is our then we can just delete this this thing okay view gridlines so this is our form then uh the next thing have to do guys let's go back to our database let's add the a table that we need to upload in app sheet so i called it let's say customize reports let's report so here i'm gonna put report type then i'm gonna add a [Music] let's say report type and start date end date and date okay start date end date then we have here a report link okay then let's delete this press it let's remove this extra column okay so what we want in report type we can just title this as all collection then start date so we will put this i will format this pass date okay so what we'll do what we're gonna do this whole collection this one we will link this this is equals to heartbreak okay and this end date is equals to end date okay so let's review b c so b and c then if you put something here let's say zero one january to 31 mar it goes here and then it goes to the uh all collection report form and also it goes here okay then what we're gonna do guys we will copy this url the one that we created a separate sheet go back to our database and paste the link in this report type when pasting the link since we want to download this as pdf we want to delete this and then change this to export question mark format is equals to pdf hit enter so we are done for this let's go to our app sheet then let's go to add a new table and look for the um for the sheets this one so i want this one customized report this case just keep it as updates only then view columns make sure that the row number is the key column report type is a text and start date should be date and end date should be date so what we're gonna do we we want to apply uh okay later on we do that then go to ux new view uh customize report let's say i will title this as cos 2 miles with port so i wanted i wanted it as detail and then i wanted to the right for example header columns let's say i want report type then all other columns then centered okay let's say i want to display this as report now let's refresh our app let's go here so all collection report so what we're gonna do if you want to let's say i want the report for start date let's say i wanted february 1 up to end of march then once you save and make sure that the link is not editable they cannot edit the link okay so make sure [Music] um save it okay so here's the link once you click the link so remember it's february 1 of march 31 let's click it will download the pdf file see this is the old collection report guys so february 1 to march 31 which is uh the same then all collection this is the system generated date and time and all the collections are here okay so you can export to pdf so this is how you export it and what if i wanted by agent let's say my cashier i want only the report for neil or ryan so we can also do that okay okay but before we do that as you notice if you edit this let's say i want to put start date february 1 sorry this one today so and then the end date is backward so we don't want to do that in order to avoid that we don't want you know the start date should be less than it should be less than compared to the end date so in order to avoid that uh let's put a valid if in our formula valid if columns you put the date so end date should be less than which should be more done should be more done or done the start date [Music] more done the value of column start date is less than the value of column column and date then you can just put here in valid selection or as you can see guy as as you like you can put whatever you like and then also the end date you can put data validity here let's face this instead this so this one should be more done the value of end date should be more done okay then you can also put in valid selection okay let's try to play the to play the app if it's work then what we can do let's other properties we have here reset on edit yes okay so you want to edit it okay okay now let's try it so what if i want to let's say fresh first see it's invalid selection if you put date today and then the end date is less done it will give you you cannot save it so you can only put it if the start date is less than and then the end date is higher than okay let's try to download the old report first pdf so here's the pdf report that you generated as per the as per the as per your selection that's it so refresh here we go okay so now what we want to achieve is we want only we want to download only the cashier name so let's it's the same thing what you're gonna do you have to add another table let's say this one cashier um clear cash here so the same thing guys so you can uh alt collection you have to copy this thing so copy this and put this here but in this time we're gonna add the insert one right third one cashier and report type should not be editable also okay so cashier start date and date so now let's apply for a formula also so here we're gonna apply a formula here same it will be it's equals to sort i always love blackpool sword and filter and the database that you want let's say this one in pressure name here is equals to this and start date sorry and the date is or equal to d2 and of course it's less than or equals to end date and hit enter so not available so let's put an initial uh here let's say nil then start date is 0 1 john to 31 march okay something wrong so let's format this as date so it's saying here miss math size heat 2 to a14 okay so see as you can see it's all nil only then what you can do you have to create a report form same like this okay i will just copy this and okay i'll just copy and add another table so this form is fair cashier form [Music] okay let's a share form and then let's pick so let's delete this so this one goes to cashier let's okay collection per cash okay your cashier for okay okay guys then here here cashier so start date should be is equals to per cashier here then is equals to per cashier end date okay then here we can put an additional name here let's say the sheer name okay it's equals to here okay then let's make a sword very per cashier here from here up to here so we don't want to include the cashier because it's already there then okay then we can delete this so this is per cashier then guys what we're gonna do same same last time for all report since we're not we wanted it per per cashier so in per cashier this one we have the customized reports here right so this starts date it should be equals to start date here and then this end date it should be equals to here and then you go to your customized report let's title this as pair actually like what is the name of your report so let's say we want to put zero one fed to 28 feb it should reflect in our cashier form here and it will generate also the report as per okay then in here it will generate also so here in customize report what you're gonna do we can add a here a column of what i did ok let's see here so we can add let's say cashier name and here in darker shade form cashier so this one is equals to here okay so if you put here a cashier name let's say meal it goes to here okay and then when you change the name let's say to arc your form change to mark and will generate only the report for mark here once you're done for that so we are already done let's copy this and go to the drive and open uh add a new google sheet then this time i want to title this as peer cashier okay here so i want to name this as report for example then i can just paste format only sorry where is it okay per cashier form copy this link the share form you make formula is equals to import range and this one for v then what is the table name per cashier form the shear form what is the range a1 to g a 1 2 g then hit enter then when it's ref allow access and then once it's generated what you're gonna do let's close this thing what happened guys wait a one to g cashier from sorry that's why it's not generated because it's wrong it could be form okay okay now it's coming then let's uh arrange these things same last time you arranged it then okay okay once you arrange it see this one on once you arrange guys let's arrange this to look it nice to make it look nicer in pdf once we export it okay so oops let's merge here we go so we are all done then we can just delete this extra column and then you show okay then copy this link where is it copy this link per cashier form control copy and go to your here customize report table and paste this link at the same time change it export question mark format is equal to pbf and let's go to our here in our app let's generate this since we added a new column so let's generate there is an initial value let's delete it okay let's save okay now let's refresh our application here in customized report so we wanted all collection it should be added now let's change our ux view into gallery okay and sort it by report type say large then the display let's change also let's refresh okay so whole collection because when you want all collection you just click this and change the date see and change the date okay but but the problem when you get all collection you don't want to put you don't want to show this cashier name so in order to do that you go to your data and in this app this report discussion name we will only show column report type is equals to what is it copy this then we wanted to make it as a drop down so first thing is we have to create our slice so new slice in collection it's a cashier name custom include only the cashier and only read the new x new view cashier name then cashier name and keep it in reference view and go back to data view columns in cashier name you keep it as ref and then the slice should be so once we go also to our perkasia report when you edit it show the report type see now let's refresh okay customize report when i want all collection the cashier is not showing okay when i want to go for cashier report so it's mark this time so i wanted it to change it to let's say i want oops so label the issue label as you can see guys the share name is labeled as this so we wanted to go to our cashier our report collection we will label label the cashier hit save let's try okay there cashier i want a report for why go like this guys okay something wrong what you're gonna do slice let's go back here let's add a table so we can add here cashier name okay what we're gonna do is put cashier and then you're gonna use equals unique unique in course and collection this up to down these are the unique name then we can just control shift it doesn't work guys so let's delete the slice then ux let's delete the cashier let's delete this and add a new table then this time the share name read only then let's go back to our report customize report view columns this cashier name i wanted to change it to cashier name yeah it's already there then hit save okay let's refresh the up okay so i wanted to download perkasia report so here i can select whom so mark also hits here not sure why it's double nil okay let's let's refresh let's open the new up there we go so let's close our database since we are done for that okay third cashier i want to add so nil i want to add nil for example then i wanted to have since let's say since march 1 to may 31st i want to see his sales i mean his collection okay then save once you save it will generate you a report a report link then you can just click okay so since this is march 1 to may 31 yeah so let's say the pdf is accurate and the cashier name is nil just wait it's downloading the pdf it takes time maybe my internet is very slow okay here we go doesn't change oops we did not arrange something wrong let's go back we did this let's try to change to ryan that's this this should something wrong here for okay something wrong with our cashier okay okay i understand again sorry guys you know why because we have to check our cashier name make sure uh cashier is the label is cashier and key column is cashier also let's change the number here let's try nil let's try it's works so it should be nil hurry changes guys okay let's try i want mark i want the report for mark from let's say january 1st to may 31st save it so it should download mark mark report okay see it's marked from gen 1 re1 to may 31st this is his total collection okay and this is the collection details and it download all the data of mark let's say we want to download the data of john the date of february 3 to let's say okay so this time i want john so john from february 28th report link you actually downloaded it so this is john this is his report so it's very uh so this is the format guys how you doing you can design as well okay so let's test our all report let's say i want to download all collection from january first up to end of may which is the my data then click this one and download the pdf so this is all report from january to may 31 okay so i hope you learned something in this video so this is how you're gonna generate the customized report in up sheet if you learn something in this video please don't forget to subscribe to my youtube channel to you know to update you for my next video thank you so much for watching and see you next time

Channel: Joe Tayactac

Views: 24,278

Keywords: Appsheet, pdf, Appsheet report, customize report, Google sheet

Id: deDkDXjSs2E

Length: 51min 34sec (3094 seconds)

Published: Sat Jul 02 2022