10 Replies Latest reply on Jul 31, 2015 8:44 PM by TKnTexas

    Setting the Invoice Number to Reset Per Month

    AndrewJudd

      Title

      Setting the Invoice Number to Reset Per Month

      Post

      Hi all, I'm working on the above. I have got everything working in terms of the month reset with a script trigger on the invoice date - the user enters the date themselves (I think this is best as they want control over that).

      However, if they click the wrong date and then click it again (or just enter the date twice) a new invoice number gets added. How can I set up an If situation to prevent this? I've tried a few different things. Attached images.

      Thank you!

      Screen_Shot_2015-07-30_at_21.42.42.png

        • 1. Re: Setting the Invoice Number to Reset Per Month
          AndrewJudd
          /files/3e834f8e23/Screen_Shot_2015-07-30_at_21.42.23.png 744x204
          • 2. Re: Setting the Invoice Number to Reset Per Month
            AndrewJudd
            /files/5981d97d40/Screen_Shot_2015-07-30_at_21.41.59.png 538x83
            • 3. Re: Setting the Invoice Number to Reset Per Month
              AndrewJudd

              I've tried something like this but it doesn't work - it locks all numbers even on new records..

              • 4. Re: Setting the Invoice Number to Reset Per Month
                philmodjunk

                Seems to me that if they change the date to a different month, they MUST get a different number--given the needlessly complicated numbering system that has been used here.

                Your script could first check to see if this is either an invoice with a different month or there is no number yet assigned (the field is then empty). If so, assign a new number. If not, do nothing.

                Only if the field is empty or the month has changed, assign the calculated value for your invoice number. There are several ways that you can track what date might have been previously assigned. One way is to define  second, not displayed date field in your table. When your script assigns an invoice number, it copies over the current value of your visible date field. That way, your script can compare the month and year of the visible date field to this hidden date field. If they are the same, no change to the invoice number need be made.

                • 5. Re: Setting the Invoice Number to Reset Per Month
                  AndrewJudd

                  thanks Phil, on a similar idea. I have a number field I am trying to set an auto-enter with the below. In this situation, it is to provide a number field for an inventory field. I want to stop the number from increasing by one if the user enters the same date for a record twice. The $$dateentry is a trigger script on the date field which returns the data as the value. Please note this is a text box not a data field, as the data is always just the year. Or could be 2011-2012 for example. In other words it needs flexibility.
                  I have tried & and AND instead of OR. No luck!

                   

                  If (IsEmpty (DATE) or (DATE ≠ $$dateentry) ;

                  If ( IsEmpty (ARTWORK 2::s_Max_Inventory_Nr) ; 1 ; ARTWORK 2::s_Max_Inventory_Nr +1 ))

                   

                  • 6. Re: Setting the Invoice Number to Reset Per Month
                    philmodjunk

                    Changes to a variable (or to a global or related field) will not cause an auto-enter calculation to re-evaluate. So that might be your problem right there.

                    Can you describe how this is supposed to work in more detail?

                    You might have to script this.

                    Please note this is a text box not a data field, as the data is always just the year.

                    And exactly which field is "This field"? The one that has this auto-enter calculation or is the date field not really a date field?

                    And I repeat: Using a simple auto-entered serial number of this type of data avoids numerous headaches encountered with fancier identifiers and still serves to uniquely identify each record.

                    • 7. Re: Setting the Invoice Number to Reset Per Month
                      AndrewJudd

                      I see, thanks. Sorry quite a long reply here!! ---- >

                      This is an age old issue for me. Something we have discussed before. Something that I need to correct and understand for the above - invoices and also all record entry.

                      I have the following in this situation -

                      1. Field which acts as identifier for each record - it's a calculation - Left(SURNAME;5) & DATE & "_" & Right ("00" & Inventory Number ;3)
                      2. "Inventory number" does this -  If ( IsEmpty (ARTWORK 2::s_Max_Inventory_Nr) ; 1 ; ARTWORK 2::s_Max_Inventory_Nr +1 )
                      3. s_max_inventory_nr is summary - max of inventory number.

                      Generally everything works fine and I'm 95% there with it. When you enter a new record by an artist it knows to add a new number according to what year you have entered. - That way each artist has 001, 002, 003 etc for each artwork from 2014, 2015 etc.

                      Two issues -
                      1. If a user enters the data twice it adds another number, so the same record goes from 001 - 002. and so on. I appreciate it's unusual for a user for any reason to click the date twice (from a dropdown) twice but you never know.

                      2. More importantly - if you have a bunch of records. Some 2015, some 2014 and so on. Then you go back and change a date of one of these you have entered it gives an old record the new latest number for the new year (that is fine in terms of the new number associate with that particular record) but if for example you have made record 003 from the 2014 records a 2015 record you will get this -

                      2014_001
                      2014_002
                      2014_004
                      2014_005

                      What I seem to require is a script trigger on the date field that replaces the inventory number for each set of records? So these steps -

                      1. Trigger when you enter field "date" - get existing date with a variable - if there is a date in it -
                      2. If no change is made to date, exit script
                      3. If change is made - find all records with that date under that artist, find and replace with numbers 1 onwards.
                      4. Use existing date variable from step 1, to find and replace the sequence numbers for that date and artist name too (as that will be out of order now / have one missing)


                      Make sense?

                      Attached is what I have got so far. Not working yet...

                       

                      • 8. Re: Setting the Invoice Number to Reset Per Month
                        AndrewJudd

                        if for any reason this helps to see...

                        • 9. Re: Setting the Invoice Number to Reset Per Month
                          BenKreunen

                          I think part of your problem is the perceived need for an ID number to contain meaningful metadata.  The primary purpose of the identifier is to provide a unique, persistent link between a record and its data. Changing IDs after they're created has the potential to cause a whole range of problems.  If you're using the ID field to perform searches (e.g. show all invoices for year xxxx), then you're technically searching the wrong field.  On the few occasions I've been asked to provide IDs that included a date component, I include a data validation step (using both calculations and manual verification) before creating the ID. I leave the ID creation step to as late as possible in the workflow to allow for edits of the data but once created the ID is locked from modification.

                          • 10. Re: Setting the Invoice Number to Reset Per Month
                            TKnTexas

                            I have read over this, for my FileMaker Pro understanding.  However, I need to comment as an accounts payable processor why would a system be created that duplicates invoices numbers?  Your customers are working with systems that check for duplicate invoice number to avoid duplicate payments.