Create Custom Invoice Forms for Healthcare with airSlate SignNow

Custom invoice forms for Healthcare

hello this is randy with excel for freelancers  and welcome to the patient manager in this week's   incredible training i'm going to show you how to  create a patient managing application complete   with drag and drop scheduling full navigation one  click appointment history patient file attachments   with preview and open full billing history with  automated invoice creation and invoicing with   dynamic taxing one-click view appointments along  with printing and emailing a dynamic menu and a   full dashboard it's going to be an incredible  training i cannot wait so let's get started all right thanks so much for joining me i've got  a really fantastic i'm super excited to bring   it to you there's something for everybody we've  got drag and drop scheduling we've got invoicing   i've got document previews and being able to open  up documents we also have a great dashboard that   we're going to show you we've got a menu here to  show you an admin screen a dynamic admin screen   fully flexible application we've got doctors  patients invoicing with automated net due dates   so we're going to be able to show you that and  i've got a whole lot more we'll be able to print   email invoices we're going to also be able to  quickly create an invoice based on an appointment   simply by clicking invoice it's going to  automatically create an invoice or it will   view the existing invoice if an appointment has  already if an invoice has already been created   for that appointment we've got patience i've  got the ability to have billing history we can   view invoices from billing history we can select  previous appointments we got drag and drop   scheduling we can view an appointment on selection  we can move appointments to a different doctor   we've got dynamic doctors navigation and a whole  lot more this is gonna be a massive training   something for everybody so i hope you'll stick  with us i just asked a few things all i need   from you is just go ahead and click that subscribe  button below and of course don't forget to click   that notification icon bell that's going to ensure  that you get these trainings and of course comment   below let me know your thoughts ideas applications  like this get built from your suggestions this was   highly requested a clinic manager be able to use  for hospitals doctors small clinics large clinics   whatever multiple staff clinics so we're going  to be able to share that with you but it comes   from your suggestions i respond to each and every  comment personally on our youtube platform as well   as well as facebook and the other social medias so  i hope you stick with us if you like this workbook   it's absolutely free all you need to do is click  on the link down below in the description and   we'll get that sent over to you either with your  email or facebook messenger however if you would   like to support this channel these videos are free  but of course i always appreciate your support a   great way to do that and to get something really  special is to pick up my 250 workbook pack   that's 250 of my best templates in a single zip  file and i also optionally including the pdf code   book so you can study the code learn it and become  an incredible application developer that's my goal   is to make you successful with excel and i'm here  to do just that all right let's get started on   this training we'll go over an overview and then  we'll get into the details because there's a lot   to cover grab your beverage of choice whether this  coffee tea or something a little bit stronger and   we're going to get started because it's going to  be a lengthy training as they all are feel free   to break this video up watch it many times take  breaks whatever because it is a longer training   okay so we've got an admin screen this is going to  help us this customize the application we've got a   staff picture folder this may come in handy in the  future but right now we're not going to use it we   haven't had a place to put staff pictures yet  attachments folder this is important because we   want to know where we're going to be putting those  attachments i want those attachments that we add   for specific patients here in the patient manager  to be in specific folders right so when we add a   add an attachment i want to make sure there's  a specific folder that we're going to be adding   and so when we add an attachments clicking add we  want them saved in individual files so notice we   have attachments and each individual tab has  a specific patient and then the patient id   i want them in separate folders so when we add a  particular attachment i want to want to be able to   preview that attachments and i want to make sure  that that attachment gets saved in the individual   folder okay so we need a separate folder for that  scheduling we want a dynamic starting time we may   want to start at different times our schedule  is based on time we also have intervals right   if we change that interval notice if i change it  to let's say a 15 minute interval right and i go   back into the patient manager notice it's now 8 15  if i refresh the schedule you see is how these now   are longer right they're longer because each row  is 15 minutes as opposed if i change it back to   30 minutes here right they're going to be shorter  when we go back and we refresh the schedule you   see everything's going to be a little bit shorter  okay so keep that in mind that is going to help   us out so it's fully dynamic schedule right  so that means a one hour appointment from 9   30 to 10 through to 10 30 is going to be two rows  so we want to be able to have a fully dynamic in   our admin screen a default appointment item a  default appointment item so we can so every new   appointment can have a default in case you want  to use one what are the scheduled staff right   we have five up to five different scheduled  staff available inside our patient manager   again we have five different staff so we can set  up five different staff right here inside our   patient manager so we have that in the admin  screen payment terms are going to help us on   invoicing if we schedule an invoice we want to do  in 15 days we'll put a net 15 and that's simply   going to mean as we add our net 15 we want that  due date automatically created just as it's done   here as we either a adjust the invoice date here  or we change the terms okay so that's going to be   automated and that's going to come from the admin  invoice settings we want to be able to charge   sales tax or maybe not we want to set that default  sales tax we also want some footer messages right   our invoice can contain several footer messages  we've got thank you for your business however if   i were to change that footer message here we want  to make sure that it's also automatically changed   down here inside our footer message so we have  those footer messages inside the admin screen   we've got intervals this is going to help us just  determine basically intervals of our scheduling   durations the duration appointment we need to  list the times right the times of when we can   schedule that we don't want to schedule it before  our start time so that's going to be important   invoice status and our payment types okay inside  our patient manager we have the ability to   of course add a patient create a  brand new patient if we want to   or we can load an existing patient just by  selecting that patient here if the patient   has a history of appointments of course those  appointment histories are going to show up here   if they've been invoiced they're going to  show up here if i want to select a previous   appointment history all i've got to do is select  here that appointment is going to be loaded up   here if i want to add a new appointment i can  click new appointment and of course we can make   changes to an existing appointment right this was  on 7 10. so i would go back to july 10th we can   see that appointment right we see that that if  i select an appointment here and i move it over   because there's a conflict i can do just that  it's automatically going to get saved and it's   automatically going to be displayed wherever it  is so if i select this we're going to have that   particular patient load up we're going to have  that particular patient appointment highlighted   and also we've got that to go on so we've got some  navigation on that i've got a billing history the   history of the invoices if i select on that  right i can create i can view that invoice   but if i want to create an invoice automatically  let's say i create a new appointment and i'm   going to create it on let's say 7 15 july 15th  and i want to assign a particular staff to that   let's say dr matthews here and i want to give it a  visit reason let's say leg pain and let's say the   appointment is going to be evaluation and we set  up a time at let's say 9 a.m and the duration of 1   o'clock we'll leave the notes blank right so it's  going to be 7 15 if i save that appointment right   and then i go to i'll browse for it on the  15th i select that we're going to see that   that automatically dr matthews and lake payne has  already been scheduled here for fred fetters that   evaluation if i select on that however maybe the  appointment is done we want to create an invoice   for that we have it we've got some invoice items  if we take a look inside our appointment items   here we've got different appointment items and we  have our item id item name uh description and the   duration the color and the cost so we have all  that information going for us and that's going   to help us out because when i want to create an  invoice for that all i need to do is just click   invoice that page is going to be loaded that  evaluation is going to automatically be created   right a brand new invoice and all i need to do is  just click save and it's going to automatically   be saved of course i can add in a footer message  if i want to i can add in a status if there's any   payments i can add those in as well and that's  going to help us and then just click save so   we can really create an invoice just based on  one click and it's automatically going to be   created here all right great so we've got that  lot to show you on that of course the scheduling   navigation we have a staff list of staff  here that's our doctors and staff list here   we have our patients a list of patients right  patient information which we'll be able to add   and of course we've got our dashboard here which  we're going to get into we've got our dashboard   it's going to be broken up into two parts  basically we have our appointment information   we have our number of appointments by staff here  we also have our appointments by type right we   want to know the appointment type let's change  this to white i think i like that white font a   little bit better and bold right so we've got that  we also want to know that our july appointments   the monthly appointments on a per day basis and  then it's broken up into our sales sales by item   sales by staff and sales by month so we're going  to cover all that and a whole lot more we'll be   going over every single detail with you in this  training we're going to start with our patient   manager section we're going to be able to add new  save and delete patients right we can simply load   existing patients by selecting them here and  of course all the information along with any   file attachments appointment history invoices  will load automatically when we have that okay   so let's get into some of the code that's going to  allow us to do that and then we'll move on to some   of the other appointments and how we're going to  do that and how we load and display appointments   okay so the first thing what we want to do is  when i noticed that when i made a change here   under e4 the patient loaded right so i've got some  information and i've got that's going to help us   do that some code but before we get into the  code we want to certainly understand some of   the formulas that are going to help us guide us  and we have some of them right here in the first   two columns a and b so the first thing what i want  to do is i've created some formulas and some named   ranges inside those formulas that will guide us  along the way so let's take a look at the name   ranges that we're going to be using throughout  this application and there's a few of them right   we've got one that's called the appointment date  i want to know what date was the appointment on   if we take a look here that is going to be  focused on all the appointment dates this   is our appointment database all those data center  forms are scheduled on i've got an appointment id   right there's a unique id for every single  appointment we have assigned staff the assigned   staff for every single appointment is located here  right each staff doctor or staff we can assign   i want to know the start date the start time that  could help us out and of course we have the data   all the data is going to be associated with there  we have appointment item id now notice that we   have individual items right appointment items will  also be able to assign colors to those individual   items those colors will be reflected on the  schedule we can quickly add it with a single click   and we'll show you that right so items appointment  items have unique ids and we can create as many as   we want they also have unique names so we create  unique names as well i've got an attachment folder   right that attachments folder is the named range  and it's located in the admin screen that's going   to help us add attachments right booking type we  don't need that it's something that's unnecessary   and we don't need this here okay so that's from  an older training okay we also have the calendar   months criteria that's for our pop-up calendar  we won't need that display type is important   right what do we want to show let's take a look at  this here inside our admin screen we have display   patient names now we may want that let's close  out of there so i can show you that real quick   now we may want that to show our last names first  or first names last right depends how we want   to display if we want to display our patient's  first name then last name i want that reflected   inside here so automatically our drop down list  is now going to show fred freder's right however   if we change it back inside to last first the way  we had it we want then our in our patient manager   reflected to that last name first first name last  so we have that here okay so i want to make sure   that we have the ability to do that and i'll show  you exactly how we can do that automatically okay   so that is in our that particular is called  display type display type as we continue in   our name manager so now that we've covered that i  also want the duration right the duration of those   appointments in a named range that's this list  of durations here and also if we move beyond the   extract those are only used for advanced filters  we had our footer message inside here we've got   our footer messages here and we also have our  footer message name the names of the footer   messages and the footer messages themselves we  have a very specific interval remember we change   the interval and i've got an interval that's  in a decimal format and that interval 0.020833 is very specific to the 30 minutes right one day  is one so half a day would be one divided by two   and if we want to know the number of hours would  be 1 divided by 12 30 minutes would be 1 divided   by 24. 24 times 30 minutes goes into a single day  that's how we arrive at that decimal we also have   an invoice appointment id right every type of time  we invoice it i want to know the appointment id   was that important that means if we create an  invoice i want to know what appointment that was   attached to that way and that's really important  and why is that important because if an invoice   got created i want to look at the appointment  the original appointment that was from so i can   click view appointment and it's going to take  me directly back to that specific appointment   with dr matthews and lake payne for fred  fetters i want to be able to know that so   it's automatically right so automatically for that  that dr matthews fred fetters it'll go back to the   original appointment so it's two ways we can go  from the invoice back to the original appointment   or we can look at the particular invoice and then  we can also view the invoice right here so we can   do that so that's another way that's a different  invoice but we can see how we can do that   very very easily so we want to make sure that we  have that ability inside there okay so back in   the name manager continuing on where we left  off which was with the invoice appointment   id that's why we want to track appointment ids  on a per invoice base we also have the invoice   date here we have an invoice id and that  is unique id per invoice and also i want   to know the patient what is the patient  name on a per right what patient was that   the total of the invoice right want to know the  total that will help us out for some that's not   correct that would be the invoice status we'll  go ahead and update that the invoice total is   located in column l so we're going to update that  right there okay so we'll be using that there for   that and then what i want to do is i also want  to make sure there we go now we're on the total   and then invoice data we have a of course  we're going to turn that into a pivot data   and we have the patient's email that was going  to show us out and let's see a few others that   are critical on this patient name and patient id  will be using those a lot so a unique patient id   and patient name those are critical everything  else is is not as important right terms we have   a tax let's go over that tax option right tax  option is yes or no right tax tax tax percentage   is a percentage tax option here is the yes or no  so we know that we can use that in named range   and we also have terms what are the terms right  the list of terms that we're going to be needing   and lastly the times the times are here so we  know all the times we're going to use those   in the schedule that is certainly important  because we need to schedule our appointments   based on a list of times so we need to drop down  data validation for that now that we have a basic   understanding of some of the named ranges  that we'll be using in this workbook today   let's go over some of the formulas how we get when  when i search for a particular patient i want i   want to know the id of that patient right the id  of the patient selected is going to be right here   so what we're going to be doing is we're going to  be using an index match now it can get a little   bit tricky but it's very very important that  we understand when we go into the patients here   we see a list of patients right here we have  a list of patients here we have a last name   and a first name and that's great but what  i really want is either to show first name   last name or last name first name right so i  want a separate list and we've created a macro   we'll get into that macro but i've created another  list right here with that full name and that full   name is based on a formula if the display type  remember that display type was either first   last or last first if it's first last what  we're going to do is we're going to combine   the last name and the first name and bring it down  so what i want to do is i want to create a brand   new list here and i want that patient id to be  brought over and i want that full name now this   full name of course is going to be dynamic based  on our formula here so when i create a unique   list here of all of our patients and i can bring  down the formula here i can then sort this list   automatically based on alphabetically and i  like that because i don't have to touch our   original data so i can combine the first name  and last name or last name and first name here   i can go by and i can also have a patient  id so i've created some named range for this   very specific range here let's look in that here  just so we can identify that and that is called   patient here patient full name here and patient  full name id so that way when i see the full name   i can know the id that's attached to that full  name because here in our original data we only   have last name and first name right so we don't  know which one so creating that separate database   and there's just a macro that's going to load this  information here and then a macro that's going to   bring this formula and bring it all the way down  only for the necessary data so we have that so   that's really going to help us out because when  a user selects this i want to know the id that   is associated with the one they've selected and  we could do that called patient search id here so   we're going to use if error we're going to index  that patient full name id right that's what i want   to extract that full name id and i'm going to run  a match based on what the user has selected in e4   and that's going to be the patient full name and  i want the first column in one now of course that   data validation that we are using is also the  patient full name so when we look in the data   validation here we see that that's patient full  name right that's that patient full name and that   way this list changes right last name first the  first name last let's go ahead and show that to   you and there's a macro that's going to do that  for us so right when i change this to first name   last and we look right back inside our patients  here we see that now these are now first name   last name right so we're doing that automatically  through a formula kind of helps us and it makes it   look a lot easier okay let's stick with last name  first name so what i want to do is i want to know   the associated id for fred fredders i want to make  sure and it's going to extract basically all that   that formula is doing is looking up fred fretters  looking here and just pulling out the patient id   from here so we're going to have that so back  inside the patient manager i also want to know   the patient id this one is going to be whatever  is loaded right so this is based on search when i   load it it's going to take this id and it's going  to place it directly here right so this one's   based on search this is based on the visible one  right here which one we're working with right if i   clear this out here we still have the selected  patient id here i also want to know the row   that's been associated with that what row is  associated with that how do we determine the   row now if we have our list of patients i want  to know what row fret is on if we take a look in   here in row 6 we see that that fred id number  three is located on row six so we want to be able   to determine that and we can use a match statement  for that based on what is in b2 here based on it a   patient id we're adding three because our first  one starts on row four i also want to know the   next patient id let's go ahead and add that in  there in case we have to add that how are we   going to do that we're going to do if air in case  there's no data at all we're going to use the max   formula and we're going to use the patient id  right the patient the regular patient id is   sufficient we're going to add 1 to that because  i want to know the maximum ball if there's no   data i'm going to put one like and that's going to  give us the next available one so when we add new   it's going to take whatever patient here inside  whatever patient id here and we're going to simply   replace it directly inside b2 and that's going  to put it there great because i want to know the   next one of course if we look inside our patients  we see we've got eight so we know the next one is   going to be 9. okay great i also want to know the  selected date on the schedule as we move this day   around i want to make sure that i have a specific  date and i'm going to place that date right here   yes that date is also right in here located here  but i want to maybe customize this sometimes and   format it so i want the original date here it's  easier for us to change here so i'm going to put   that in b5 if i've selected an appointment i  want the id of that appointment located in b6   i want to know the row of that appointment  that's on the database on row 5. and that means   appointment id 2 is located on row 5. where  we look at our appointments and we look at   our appointment id2 and it is on row five um again  just like we did with the patients we are going to   be matching that right we need to use that match  and we're going to be adding three because our   first one starts on row four we're matching  whatever's in b6 that's our appointment id   using the appointment id named range if there's an  error we're going to put blank again i also need   to know the next appointment id and we're going  to use the max formula to figure that out for us   just like we did with the next patient again also  the invoice id if i've selected it if there's an   invoice attached to this i know that this has been  billed out right this is where name drainage comes   if i select disappointment i want to know  there's no look there's no invoice attached   to this however if i select this appointment or  this appointment there is an invoice id how do i   know that well we're going to use this formula  what we're going to do is we're going to index   all the invoice ids right i want to match it  based on the invoice appointment id what does that   mean that means i'm going to be looking up this  particular appointment id 17 and i want to know   what the invoice id associated with that if we  look inside our invoice list here all of our data   and we take a look inside our here we see our  appointment idea 17 appointment id 17 and we   know our invoice id is 20. so anytime i see 17 as  the employment id i want to return 20. i want to   know that there's an invoice associated with that  if it is found right sometimes it not it will not   be found so that means here appointment id 17  i want to know that it's invoice id 20 so we're   using this formula to extract it and why is that  important because if i decide that i'm going to   click invoice i want it to go directly to invoice  id number 20 right so here's 20 20 right however   if it has not been created yet so let's say this  one here let's find one okay this one here has not   been no there's notice no invoice id right here  so i need to know that when we decide we want to   create an invoice for this appointment is going to  be a brand new invoice when we click here the next   invoice available is 52. it has not been saved yet  right so we know that it's not there so as soon as   we save it so what it's going to do it's going to  take all that information right it's going to take   this deployment item it's going to take the  assigned staff it's going to take the name   and it's going to create an invoice based on all  that and it's almost ready to go if we want to   add more items we can however all we need to do is  just simply save it add a footer message or status   or payments if we want to so it's a very quick way  so we need to understand inside the macro whether   it has been created or not and this particular b9  is going to let us know if it hasn't been created   all right appointment request required field this  one i want to know the number of required fields   for appointments certain when i create a brand new  appointment i try to save that there's a minimum   number of fields that were required we need to  make sure we add a date a staff and a time before   saving that and that's a date here a staff here  and a time here so these three are required   easy way to do that is simply to use count a for  those specific cells if that number is less then   of course three then we know that we need to let  the user know that those are required as soon as   i select an appointment and you'll see this goes  back to three because those particular fields   have values in it and they have been counted  if i click new appointment and i add a date   here we see that that's going to go to one if  i add a signed staff it's going to go to two   and the last required field is of course right  here 9 am and now it's going to go to 3 and it   will allow us to save that appointment all right  great so we understand the importance of this   required fields i also want to know these are  going to come in when we come into scheduling   we'll cover that right i want to be able to  drag and drop appointments very very easily   i also want to select appointment and i want  to be able to change the duration if i want to   and then save that appointment i want to make sure  that that schedule updates automatically unsafe so   we'll get into this and then i also want to know  the selected attachments row if our let's if our   patient has attachments i want to know what row  they've selected this is going to do that that's   22 right so the selected row it's going to trigger  some conditional formatting and it's also going to   let us view it if i want to view it or maybe we  want to delete an attachment we can do that oops   let's fix that i change the macro for that so  let's go ahead and fix that up that is going to   be in the patient macros here and we have one now  change the name of that patient macros right here   load attachments we'll be getting into all this so  we have four different modules this is the one i   want and we're going to update that okay so that's  going to cover rerun that so we want to be able to   are sure you want to delete this attachment yes  okay so we're deleting that and that attachment   gets deleted and then we're going to be able to  refresh the other so we can delete attachments   a great way to do that i also want to know the  selected invoice row if i've selected a specific   invoice i want to know what row we have selected  that is going to be located right here in b21   conditional formatting is going to trigger on that  and it's going to show so i've got this one this   conditional formatting is real and this one are  relatively easy this one is unique and fun okay   so these conditional format if we go into here  we see conditional fry simply based on the row   we're coloring it if you've seen these before  simply based on b21 okay and also likewise for   this one we're simply coloring it based on b20  here inside conditional formatting however the   one that i want to show you is a little bit  different right so now what i have is i've   got some hidden right here i've got some hidden  information here if i go and change the format   here and i click general here on all of them let's  select all of them general right we see that we've   got some numbers here now and those numbers are  of course our appointment ids associated with this   so if i've got some appointment ids here and i  know that this appointment id is one it's brought   over but normally it's hidden and i've got that  appointment id right here if we find a match i   want to color that line so that if i select here  appointment id 55 loaded i'm going to take this id   i'm going to put it directly in here and then i'm  going to load this appointment information right   so what i want to do is i want to have to make  sure that we're running a conditional formatting   based on that so how are we going to do that  well let's take a look inside the conditional   formatting and we're going to manage the rules  we're going to take a look and it's going to be   two conditions under that the two conditions  are and we want to make sure that b6 does not   equal empty b6 does not equal empty and also b6 is  equal to f11 however f in the 11 is of course not   absolute it's going to be for every row as long as  our applies 2 starts in row 11. notice there's no   dollar sign before the 11. that means it'll apply  to every row so if we see our applies to is g11   through j17 so our applies to starts at the same  row and that way we can easily automatically   conditional formatting anytime whatever we have a  match from the id here and the id here and and of   course our macros now we want to hide this data  we don't really want to see this data all we need   to do is just go into the home use a custom  format under more formats right here and just   use three semicolons one two three and then it's  going to automatically be hidden and there it is   so it's hidden there so we don't need to see  that so that's how we're using these three   different conditional formats this is our sample  shape it is this shape that will be duplicated   in order for us to create this schedule we'll be  getting into that in the schedule okay so let's   give a start on this a particular patient  information notice when we make a change to   e4 we see that that patient low information loads  up along with that so let's get into the macro   going on that and of course that's going to be  inside our developers and visual basic right i've   got different modules we're going to start off  with the patient macros here right up here and   we're going to start off with all we'll start up  all the way at the top it's a little bit easier   but what i want to make sure is that we understand  our course dimension variables we have last row   and last results are as long patient row i want  to know the row of that database the column as we   loop through and the selected row what row we're  selecting and the patient name as string okay   when we create a brand new patient right want  a few things to happen i click add new i want   to clear it all and i also want to make sure the  patient type if it's the default notice inside   our here right inside our patient we have some  information here patient defaults and details and   we have a default type patient type as referral  if i were to change that to walk in or leave it   blank but i could change it to walk in and i click  brand new patient here i want to make sure that   that patient type goes default to walk-in okay so  we've got a few things happening here basically i   want to clear the contents of all these i want to  set the default to work to notes and and i want   to set the default so that's all we're going  to be doing inside our brand new patients and   i want to make sure that everything else gets  cleared out right if we've got a new patient   i want to make sure the appointment information  gets cleared out the history the billing history   and the attachments and perhaps any preview  that might show up i want to make sure that   that's all gone so we're going to do that inside  this first thing all that attachment preview   remember we saw that x-ray that's always going  to be given this name called attachment preview   so we're going to delete that we're focusing on  the patient manager now but if it doesn't exist   it could create an error so we always must wrap it  in on our resume next and on air go to zero we're   going to clear out all of those fields associated  with that particular patient the history the   invoice history and the appointment history i  also want to set that default text to notes d16 is   going to get on that default text i want to make  sure that this gets the default text i've got some   conditional formatting in here that if the test is  exactly notes then i want to make sure to color it   italicized and i want to make sure it's gray but  only if it's notes right as soon as it changes   to something else i want to make sure that that  gets black and your normal font okay only notes is   going to take on that italicizer just to help the  user along okay so it's going to get that default   text right notes and of course we also want to  make sure that default patient type is going to   come in here right inside from of course our admin  c19 that's going to come directly into e7 right   that is our default here right here c19 is going  to come directly inside here and it's going to go   directly inside our default patient type which is  located in e7 okay so it's relatively simple then   i just want to simply select e5 that is going  to allow the user to start entering information   along with their last name and then their first  name very good next up we have the save when i   make a save i want to make sure that we also have  minimum fields i want to make sure that there is   at least a last name and a first name that's got a  minute if i try to save without that it's going to   say please make sure to add in the first name and  last name so save is what we're going to be doing   and we do that if e5 or e6 is empty we're going to  let the user know we're going to exit the sub out   right now what i also want to do is i want to  determine it's the same macro that we're going   to be using for both saving or updating existing  ones if i want to make an update i can do that   very very easily but i want to differentiate  between whether it is a brand new one or an   existing one and how do we know that well we got  a patient row right here if this patient row is   empty we know it is a brand new one however if it  is an existing one we know that it's filled in so   b3 is going to let us know that's going to be our  differentiator so we can do that here if b3 value   is empty then we know it is a new patient okay  otherwise it is an existing patient okay existing   patient will be down here so for new patients we  want to create a brand new row inside our patients   database the first available row we've been over  this a few times right so inside our patients   here that's doctors but i get that correct and the  first available row would be here in 12. so i want   to create that first available row here and then  what i want to do is i want to make sure that we   set that patient id that new id where's that new  id going to come from it's going to come from   b4 using that max remember b4 here we're going to  use that max formula and it's going to go directly   into b2 we also want to take that patient id  and i want to place it directly inside our first   available row inside column a right here okay so  then i also want to place first name last name   so that means so basically all we need to do with  new patients if i want to do a new one let's just   do johnson and then fred and then i want to save  that right i want to make sure that that brand new   name gets saved and i also want to make sure that  it gets loaded here and i want to make sure that   his brand new one his last name is first name  and of course the type was automated so i want   to make sure that they show up correctly in the  right rows okay very good so we understand that   the differentiator but the differentiator is we  want to set that patient id in b and the patient   row it's going to be from b2 right i want that id  to go in column b excuse me column b because we're   starting it off right here okay great so column b  column a we're not using and then of course c and   d take on that so but what i want to do now is i  want to make it a little bit easier if it is where   we're saving the data now we're going to save  the data regardless if it is a new or an existing   however if it's an existing all we need to do is  extract that row directly from b3 now what we i   want to do is i want to use something called  data mapping however there is no data mapping   if you look in here however what i want to do is  because i've aligned things correctly here if we   take a look here last name first name all the way  from birth date and then to notes everything here   is in the exact same order as it is in here last  name all the way to birth date and then notes okay   so we know that this is column three column three  four and we also noticed that this last name is on   row five right so we if we know that we're  going to go all the way to row 15 we can   easily take whatever data is in here and place  it directly inside our patients through a simple   loop so that's just what we're going to do for  the patient column equals 3 to 13 right we're   focused on the columns column 3 we've already  done the patient id so we're starting on 3   all the way to 13 right birth date is 13 and  then we'll do notes a little bit after that   so what i'm going to do is i'm going to run a loop  and i'm going to take a look inside column e and   the patient row the rows 3 but this is 5. so we're  going to add 2. we're going to look at that we're   going to place it all the way down here so how do  we do that well we can do the patience the cells   the patient row and the patient column we know the  row we've already extracted it from here or here   the column's going to loop from 3 to 13 and it's  going to be e and the patient column starting at   3 but we know our first data last name is on 5  so we need to add 2. so it's going to be e5 and   it's going to add or update patient using that so  if we create our user form on our sheet just very   very simply not skipping rows making it simple and  making sure it's in the exact same order we can do   that that's another way of data mapping without  actually having data mapping and then notes is   last because notes we're using a full so we're  just simply going to use one row for that one line   of code to take care of notes and that's right  here if d16 value doesn't equal notes right if   it equals notes that means it's just the default  text we don't want to continue if it's anything   other than notes then what i'm going to do is  patient the column and the patient row is equal   to whatever's in d16 now what i want to do is i  want to know whether we're going to be adding in   our display now what i want to do is that brand  new one i want to put him up here directly here   right so notice it says johnson fred right i  want to make sure that he's available in fact   i need to run one macro right after we saved it  to do just that let's go ahead and run that macro   i've created a macro that's going to update those  names very very helpful and that macros down right   here right here it's called patient sort list now  i wanted that list updated if we noticed that new   patient wasn't added in the list right take a look  here you saw the mesh come up johnson fred is not   here in the list right because i want to make sure  that we add them so we're going to run a macro   we'll be going over that now and that macro is  simply going to take that name it's going to add   it to this list right here going to add it to this  list right here notice he's not there or probably   added up here so we're going to do just that now  so after we save it i want to make sure that that   list is updated so what i'm going to do is i'm  going to go up here to the patient save right   at the end of this macro right before the fade  out message i'm going to create the sort list   run sort list and we're going to go over that  macro next but i want to make sure that it runs   and then so we can have that displayed so we're  going to go ahead and paste it i'm just going   to save it again if we take a look now at rand  take a look in here we now see that johnson fred   is here right we now look inside our patients  and we look over here we see that is also here   so we have everything we need right there and it's  automatically in the list okay so let's continue   on and i'll show you exactly how that's going  to work but what i want to know is the display   type now that display type from our admin screen  is either first last or last first if it's first   last what i want to do inside directly inside  this cell is i want to put the first name a space   and then a last name however if inside the  admin screen it says last first then what i   want to do is i want to put the last name comma  and the space and then the first name i want to   put that directly inside here so to do that all we  need to do is just do this if display type equals   first last using the brackets is fine for that  named range then the patient name is equal to e6   right meaning the la the first name e6 is  our first name a space and e5 which is of   course our last name so it's first name last  name however our patient name if it's last   first our patient name is going to be e5 our last  name a comma a space and then the first name in e6   so we're going to set that full name then all we  need to do is take in e4 as long as it doesn't   if it doesn't already equal the exact same name i  don't need to place it there it's already the same   but if it's different if e4 is different than  whatever is a page name then take the 4 and   place that patient name right in there and that's  going to automatically update we're going to do   that one more time right so i want to add in the  last name let's go add in johnson this time and   then put in harry okay so now what i want to do  is i want to save that and we notice that johnson   johnson harry got done right here and we see that  it's now in this list automatically okay great   great so we've got that we show you how to do that  so that's going to happen there and then we'll run   the sort list and then this fade out message that  fade out message just simply going to take this   shape every time we save it we see it up here  on the upper right see it says patient saved and   it slowly fades out and we're just going to run a  loop to do just that that loop is going to change   the transparency as we loop through it and then of  course the visible is going to be high that's all   we have to do there patient load relatively simple  right when do we want to run the patient load   well i want to load all those patient details  when i put that first name directly in here   when i make a change to e4 i want all that  patient information to load here it's a change   on e4 so let's take a look inside our change  event because it is the worksheet change event   inside the patient manager here we're going to  be focused on worksheet change event when i make   a change to e4 i want to make sure the e4 is not  empty and that happens i want to make sure also   that b1 is not empty what is b1 b1 is going to  tell us based on that search based on that search   if b1 is empty that means there's an error  it wasn't found that full name wasn't found   then i wanted to let the user know right please  select a patient from the drop down list if they   enter anything else v1 would be empty that's going  to upset okay if it's not empty all we're doing is   taking that id extract it from b1 and placing it  directly inside b2 so that search for id here is   going to be put inside b2 which is the visible  id once i have that it's automatically going to   create a row here through the calculations where  that once i have that row i can load in all that   patient data right here okay so that's what we're  going to do we're going to run that that macro   patient load is next as long as we bring over that  patient id and that patient loads right here so   the first thing we're going to do is clear out a  bunch of fields i want to make sure that b3 does   contain a row remember b3 is critical i've got  to know that row associated if it is empty then   the user has not selected a correct patient so  we'd let the user know to please select a correct   patient to view so the patient rows equal inside  a variable long variable b3 and again again we're   going to run a loop just like we did before but  this is going to be a reverse loop that is mean   we are taking the data from our database from our  patient row and patient column and we're bringing   it into e and the patient column plus two and  remember it's the same thing because we're   starting it off at e5 e6 e7 and bringing all the  information over and lastly a single line of code   for our notes because it is not adjacent it's in  a different column here so it's if it was in the   same column fine we could do it but i've chosen  to add a larger area so d16 will take on that   then what we're going to do is we're going to  run a macro to load attachments we're going to   run a macro to load the appointment history and  another macro to load the invoices because that's   going to load that employment history load are  any attachments that we have in here associated   with this particular patient and load in the  billing history associated with this patient   as well and those are in these macros ahead to do  that i really want to extract the information for   example the appointment history for this specific  patient or the billing history for this patient or   attachments for this patient so let's go through  that inside our appointments right if you notice   inside our appointments table here we've got a  patient id so we can easily extract that using a   criteria here patient id having those results come  through an advanced filter pace history and then   pulling out some of or all of the appointment  history into our main screen likewise inside   our invoice list we can do the same thing right  i also have a patient id here we can then extract   that using advanced filter and criteria patient  id having the patient history come and also just   like with the attachments exactly the same our  attachments also contains a patient id using a   criteria we can have those results come here so  that's exactly what we're going to do inside those   three different macros bringing that information  inside our main patient screen here okay so let's   get to it inside our macro also inside located  our patient macros here the next one up right   is loading attachments so we're going to go over  the three particular macros and it's important   to separate them into individual macros because  there are times when you only want to load in load   attachments for example when we add an attachment  we don't need to refresh the entire patient we   only need to load that attachment or for example  if we adding a specific appointment or adding an   invoice we only need to reload the individual list  and that is why they are in separate macros so the   first thing we always want to do is clear  the original data so for the attachments   we also have here a attachment id here notice that  they're hidden just like they are everywhere else   right we're using a custom formatting with  three semicolons if we change that to general   we will see that we have the attachment id six  five four i'll undo that with control z or i'll   do it or actually the macro run so i'll go ahead  and go back into more number formats six five four   while we are running this i'll guess i'll keep  it available for you so you can see it those   are the specific attachment ids here right we have  individual rows here four five and six here and we   have our our attachment either so we want the rows  i want to know what row they're associated with   attachment we don't have an id so we know if we  want to delete them i know which row to delete   so i'm going to bring in those rows and i'm going  to bring them directly inside our patient manager   here and put those rows directly in here so i know  that the database row that's associated for that   okay so likewise the first thing we want to do is  clear all the original data our original data here   is located from c21 through d29 so that's exactly  what we're going to do inside here c21 through   d20 we're clearing the contrast and then what we  want to do if there is any preview this preview we   want to make sure that we are going to delete  it this particular is called the preview this   is the picture that's going to show up this is the  picture that's going to be displayed regardless of   what we're selecting it's always going  to be called attachment preview okay   so that's the given name and anytime we want to  add or refresh this list i want to make sure that   that gets deleted if it doesn't exist it would  create an error therefore we've wrapped it on air   resume next and on air go to zero we're focusing  on the attachment sheets with the attachment seats   that is where we're running our advanced  filter if i bring this over here we don't   have to stretch it across i'll bring it over  here and now we can see it here so with those   attachments right what we want to do determine  the last row and we want to set up criteria now   we can link this criteria directly linked to the  patient manager b20 that b2 that is our patient id   gonna have the advanced filter come results  come in here we'll set a maximum number of rows   working with a limited area of space i'm working  with just a limited so this is like a compact   con so we have a limited space for our history  appointment history and i'll explain how you if   you have additional how you would want to  see that okay so we have pretty much here   nine different rows so i can bring the nine rows  in and include of course that row here once we   once we get those results so that's exactly what  we're going to do inside our macro here determine   the last row if it's less than four that means  we have no attachments and that is because our   first attachment starts on row four here so if  it's anything less than that we know we don't   have any data yet so we can then determine an  advanced filter a3 through f in the last row our   criteria is going to be h2 through eight through  h3 and our results are coming from j2 through k2   so that's exactly where we want j2 through  k2 our results are criteria's h2 through h3   getting the results then we're going to determine  the last row of results and or we can in this case   we can just bring over nine rows right if i can  bring up we may not need to know the nine rows   if whether it's one or nine or if it's a lot  we only have a maximum and limited in nine rows   so really all i need to do is just bring nine  rows of data regardless and that's exactly what   we're going to do inside here although we did  determine the last results row because we need to   i want to sort it i want to know the most recent  one first right i want that the one that they just   added whichever one that might be i want that on  top and then i want it to go in reverse so how do   we know that well we know that the last one the  last one they added is going to be at the bottom   and it's going to have a row whatever row that was  so if i sort reverse descending based on that row   that latest one is going to be up at the top  the the oldest one is going to be at the bottom   so what we're going to do is we're going to sort  it but i don't want to sort if we don't have any   results row or if we only have one row of results  if we only have one row certainly we don't need to   sort it so that's what we're going to do so the  first thing to do that is we're going to clear   any sort and then we're going to add a k which is  j3 actually j3 is that first result and we also   want it descending because i want the highest  first so that order is going to be descending   i'm going to set that range make sure that we  list out oops this is wrong not patience this   should be of course attachments right attachments  right i want to make sure still works but i want   to make sure that we are on the right sheet we  need to call out that sheet again because we   are inside another width notice we're inside the  with sort so we want to make sure that we have   the correct sheet j3 through k and the results row  okay that's going to sort it automatically newest   attachments first once we have those results it  doesn't matter the last results row i'm just gonna   bring over nine total rows even if they're blank  that's fine too so we're gonna c21 through d29   equals j3 through k11 that's it just the eight  nine rows there i'm going to bring that over all   the way over bringing over those attachment data  right there and that's going to bring it directly   whatever results are even if it's nine even if  it's blank right which is bringing down all the   way over all the way from j3 through k11 is going  to be equal to here inside of course c21 through   d29 and likewise you will not see these rows  number there's no reason to see those so we'll   just change the format to three semicolons that's  going to show that the end user does not see that   as as they would not want to see that and that's  okay so we can do that we can also change the font   color to the same as the background that would be  sufficient as well so that's how we bring in our   attachments okay now continuing on we also have  our appointments loading can be relatively the   same once we do this a lot it can be easier again  our appointment ids are located here in column f   so we're going to clear out the original data  from f11 all the way down to j17 inside the   appointments we're going to run that criteria here  results are going to come here we're bringing in   just nine rows of data we're just going to sort  it i want the newest date first all the way to   the last date then we're going to sort it bringing  in only those newest ones we have a limit number   nine so we're going to bring in all of those  and that's exactly what we're going to do   and so inside the vba that's exactly what we're  going to do we're going to clear out the existing   appointments here then we're going to determine  with the appointments database the last row making   sure that that we have data our advanced filter a3  through j in the last results row that's going to   create our advanced filter with the criteria  of our patient id automatically linked to b2   q2 through q3 and then having those results s2  through w2 that's going to bring in all those   results here i'm going to determine the last  results row if it's less than 3 we're going to   exit we don't need to move forward if it's less  than 4 that means we only have a single row we   don't need to run sort we are going to run a  sort based on the correct sheet appointment   database here t3 is where i want and i want it  descending t3 is our date right that's the date   of the appointment i want that descending meaning  our newest appointments first because that's what   i want to see on top our oldest appointments on  the bottom okay that's going to run that sort   and then all we need to do again is just once we  finish that sort just bring it over those nine   rows of data or eight in this case i believe rows  of data is going to be automatically added to that   through one single line of code here and  that's going to come all the way from f   here to 11 all the way through j 17 equals simply  our results data here located in our appointments   all the way through here and that's it that's all  we need to do to bring over our results and it's   going to come from and our p