Streamline Your Workflow with a Duplicate Invoice Example for Teams

Duplicate invoice example for teams

hey you guys guys Stevens again this has been a long time since I've made any videos but I just had a question from someone who asked me how you would go about in an invoice to duplicate the invoice and to duplicate all the items that are in the invoice as well and to kind of keep them all together on the new and duplicated invoice and I thought I would reply to him but then I thought i' probably be quicker to just do it and show it um so I I have this invoicing database that I've done a little while ago and if you go to file manage database you can see it's extremely simple and this was just like probably also a simple example I made for someone and it just uh it has a client table that is related to the invoice table and the invoice has just an ID a date and a client ID then that invoice has line items related to it using the invoice ID FK field this has products on it from a product table and it just has the amount the price lookup and the total so it's very simple very basic but what would you how would you make a duplicate of this invoice but keep all the items on that new invoice as well um you would for instance make a button that says duplicate oops do invoice okay now what would how would this button work now it's a bit tricky because you've got an invoice and you can very simply just um duplicate a record by going to here and but then you will have a new record but nothing in it so what you need to do is now that you've got a new invoice you will also need to duplicate the records that are in there um how would you go about doing that I think you need to make a script but in order to find out which script set steps you need to take I think it's probably best that we just kind of try and figure it out m manually first and then we will learn which steps we need to take so what I'm going to do is I'm going to go take a piece of paper to write steps down and then I'm going to go in my invoices uh table here and I'm going to set it in table View and then let's figure it out so the first thing to do will be to find our first invoice and I only have one invoice here invoice ID one and now what I have to do is basically duplicate these record and they need to become if I look at my invoices table so I had invoice number one and now I have the new one is invoice number two I will now have to uh basically make copies of those but change this value so that it is now uh so that those new ones are assigned to the invoice number two and not to invoice number one so what do I do I go to my line items table in my script I find all the in all the line items that are um so I'm going to write these steps down and I will have to uh look for all the uh line items from invoice one and then duplicate them so let's try and figure out what that does I will be on my first record here what happens if I go record duplicate record then I will get a new one um which will actually be is this one duplicated it will arrive at the bottom so I will then have to set set the invoice ID to two Okay then what I can do is maybe um because I don't need this one anymore I could hide this one I could say record um omit record then I go back to the first record I can do that there's a script step for it and I've done this one so this one can also be omitted so omit record then this one needs to be do duplicated record duplicate record and then this one also needs to be set to invoice ID 2 etc etc so this is basically you've got kind of the same action that's going on over and over and over again you don't know how many records there will be so you have to make sure that that can be variable and then you've got these steps of duplicating the first one then omitting that one omitting that one then going then you will go back to the first one and omit that one as well because that one is done and then you will have the last one which you will then also duplicate change the invoice ID and then um you go and you just continue you omit this one and then you go back to the first one and you omit that one as well and when all the records are gone I had three in the beginning now I have six uh let's see what this looks like on my invoice so if I scroll back and forth from invoice one to invoice 2 I have now correctly duplicated my invoice and also duplicated all my line items so let's make a script for that and let's call it tocate invoice now this I do have to tell you this is the very easy way to do it of course in real life you will have to deal with different stuff as well like for instance do you have a difference between a price offer and an actual invoice does your actual invoice have an invoice number that needs to be unique if that is the case then you need to maybe when you duplicate your invoice maybe you need to calculate a new invoice number or maybe that invoice that you duplicate needs to then become first the price offer before you turn it into an invoice or stuff like that that's stuff you're going to have to kind of figure out uh for yourself for this very simple exercise we're just going to um do uh do it really simply so what do we need um we've got one invoice and we're going to make a second one so we're definitely going to have to duplicate um our invoice but we need to think ahead a little bit because we're going to need to have uh we need to find all the line items of our first invoice and then we need to rename re assign them to our second invoice so we're going to need two uh invoice IDs the first one we're going to need is going to be let's say [Music] invoice ID old that or original maybe that's the first one and that's going to be our invoice ID and then we need to make sure that we set that one first because then when we duplicate our record we will arrive in our second invoice ID so what I'm going to do is I'm going to copy this one contrl C I'm going to paste it again and I'm going to set a second variable invoice new invoice ID new and that's going to be the same one basically we set the uh variable we we remember because set variable is like a remember script step we remember the ID of our first invoice our original one we duplicate it and then we remember the ID of our second invoice okay so now we've done that now we need to go to another layout and usually what I do when I go to another layout I will freeze the window because I don't really want to see my window flicker or go to another layout so I'm going to freeze my window I'm going to go to layout and I'm going to go to my line item then I will enter find mode and I will uncheck my pause button because I don't want to pause there I want to immediately set a field and I want to set in my line items table I want to set the invoice idfk to the value of what do I need to search first my old in my old line item so my invoice ID old dollar sign invoice ID all and make sure you don't make any typos with because otherwise it's not going to work so I'm going to enter find mode set field then I'm going to perform find which is pay attention is under found sets there is also a perform find replace that is under editing but you don't want to use that one so you just use a perform find then you should find all the line items um of your original invoice and then what are we going to do then if you remember correctly we need to go to our first records just to be sure so let's do go to um record go to record request first so we're going to go to our first record and then what we're going to do is duplicate that one so let's go to records and duplicate we're going to duplicate this one then when we have duplicated this one we need to change the invoice ID so we're going to use a set field and we're going to set field that line item invoice idfk to our second our new invoice ID so dollar sign invoice ID new then when we have changed that one we can omit that [Music] record under found set I think that should be found and then we go back back to our first one go to rec request page first and omit that one as well and then we basically automatically arrive at our second line so then we kind of need to and the problem is we don't know how often we need to do this so uh then I will that's basically you already have a repetition here and you've got the same thing here so now we're going to have to figure out how we need to make this uh thing happen over and over and over again until it needs to stop so we've got a loop function for that and I'm guessing we're probably going to Loop somewhere here after that first go to record request page first because we've got that one here as well so we get a loop and we get an end Loop and I think the end Loop is going to be somewhere here and I think we need to make sure that we this Loop happens we duplicate a record we change the invoice ID we omit that one go to the first one and omit that one as well and then we basically continue duplicating and omitting and stuff like that I think this is looking pretty good but we will only know if this really works when we test it out one thing you need to be very careful with when you do a loop you need to have some way to exit that Loop because um if you don't exit this Loop this Loop will go on forever and you will be kind of in trouble so we need to have an exit Loop if and I think a good one would be if all the records are gone because if you remember um we um when we continue when we did this thing manually eventually our table was completely empty so we can use a get function for that and a good get function for this would be get the amount of records that are found so get found count equals zero exit Loop if get found count equals zero and then we exit our Loop then we will be in the line items table that will be completely empty so we want what we want to do then is go back to our original layout um looks kind of good to me I think we need to try this one out so we need to save this one contrl s and then what we can do is either just go for it and try it or if you really want to see what's going on and you have FileMaker Advanced you can use the script debugger and the data viewer uh I think I had not yet assigned the script to this button so let's go to button setup perform script as I duplicate invoices and let's just go for it and see what this one does right so now we have invoice number two and let's just go for it and we've got our script that starts to run here but when we have our script debugger open the script always stops and it allows you to kind of Step through it one by one so will I form this script yes I do I want to step into this script then what happens I've got my set variable invoice old so in my data viewer I can now see what's happening with my data so my invoice ID is um now two and so my invoice old variable also has the value two then we're going to duplicate This Record request and when we step you can look here our invoice ID is just changed to three this is here is empty so we've just duplicated our record now this invoice ID is three so the set variable invoice ID new will be making a new variable and it will contain the number three so now we've got our old and our new invoice IDs that's good freeze window is not really going to do anything here because we can keep on looking what's going on we go to our other um our other layout we enter find mode we set our field invoice ID to our old invoice ID we perform our find and then we find three records all for invoice number two that's all looking good and then we start our Loop now this is exciting let's see what's going to happen we duplicate our record we're in the first one so that's good because we went there here so we duplicate yes that's looking good we have a set field that changes our field here we omit this one we go to the first one we omit that one and then we exit our Loop if our found count is zero but our found count is two so we should continue in our Loop yes we duplicate we set field omit go to the first one omit we exit the loop well not yet duplicate set Fiel omit omit exit Loop if found count is zero yes that does it and brings us back and let's see what we've done here we've got invoice number three with our three items let's skip back Back 2 one so now we've got one that we copied manually and one that we copied by script so looks like this works pretty cool