8 Replies Latest reply on May 21, 2013 12:22 PM by ChristofBirkenmaier

    Complex: How to generate customized, composite invoice references ?



      Complex: How to generate customized, composite invoice references ?


           Hi Everyone!

           Last week I felt adventurous and decided to move the management of my small business, which consists of various types of consulting for various customers from an MS Excel / Word - based operation to Filemaker :-)

           Needless to say that I am a total novice to FM and I am grateful for the help that I have received through this forum sofar.

           Now I am struggling with the next hurdle and I have spent the weekend reading up in the knowledge base and the users guide - to no avail....

           My database is structured in 2 tables: One table that holds the information on all customers and another table that contains the information about all business transactions. They are linked by a match field called "Auftraggeber" (customer, see attached image). The table that holds the customer data also specifies a unique 3- or 4-letter codes for each customer, addresses, fax numbers, etc. From within the layout in which I enter any new business transactions, I can select the customer for that transaction by means of a drop-down menu, because I linked this via a value list (specifed to the customer table).

           The problem ist the following: For the past years, I have consistently assigned a customized reference number to each invoice, based on 3 components: 1: A 2-digit representation of the current year, e.g. "13"; 2: A 3- or 4-letter code for the specific customer, e.g. "AGMM" or "LGA"; 3: A running serial number of the invoices sent to the specific customer within the running year.

           I could of course either change my stratgey or continue to do this manually without too much effort, but it would be cool, if this could also automated within FM.

           My specific problems are the following ones:

      1.           How can I derive a 2-digit-year code (YY) from the current year?
      3.           How can I have the specific code of the customer for a particular transaction show up in a usable form, since this code resides in the customer table? I've tried to work on lookups, buy was unsuccessful. I tried a merge field, but this somehow cannot be used in a formula. 
      5.           How can I have a script or a formula count the number of occurrences of the pertinent customer code for a transaction in the database field for these codes ("Kürzel Auftraggeber") and return that count? (It would be acceptable, if that were a continously running count, irrespective of tax years).
      7.           How can I have FM combine these 3 components into something that looks like this: "13-AGMM-5" or "13-LGA-12" ?

           I'd greatly appreciate any help on this, but I realize that it may either not be feasible or worth the effort. If that should be so, I'll just generate and enter these references by hand. That would be somehow uncool, though.

           Thanks in advance for your thoughts and for any forthcoming suggestions!



        • 1. Re: Complex: How to generate customized, composite invoice references ?

               Hello to everyone who read my post!

               It appears, that none of you had a good answer for my quest and I also haven't gotten any further with more reading up.

               But maybe, I also just wrote too much text volume and formulated my goals too specifically for you to really want to read it all.

               Therefore another attempt to get help with fewer words:

          1.           Is there a function in FM, that will return something like "YY" for the current year (eg: 13 for 2013, 14 for 2014, etc.) ?
          3.           How can I retrieve the contents of a specific text field (above: "Kürzel Auftraggeber", eg: AGMM or LGA) based on the above depicted relationship between 2 tables ?
          5.           Can such 2 components be combined or fused into a new text field that will also count the unique occurrences of each combination (13-LGA-1, 13-LGA-2, 13-LGA-3 and so on) ?

               Again, any help - even if just a partial solution - would be greatly appreciated!

               Thanks in advance, Christof


          • 3. Re: Complex: How to generate customized, composite invoice references ?

                 1) Right ( Year ( Get ( CurrentDate ) ) ; 2 )

                 2) There are multiple means, it depends on context and exactly what you need to do. From the context of Gutachten--either a calcualtion set to refer to that table occurrence in the Context dropdown or from a layout based on that table occurrence, you can simply refer to Auftraggeber::Kürzel Auftraggeber. For a layout based on Gutachten, you can simply place the Auftraggeber::Kürzel Auftraggeber field directly on the layout.

                 3) Certainly and there are multiple methods for this as well. It depends on how you will use that and the version of FIleMaker you are using. ExecuteSQL, is one such possibility but requires FileMaker 12 or newer.

            • 4. Re: Complex: How to generate customized, composite invoice references ?

              smiley WOW....thanks a lot for these great suggestions! enlightened

                   Dear jm, thank you for the great example file! The CalculatedResult fields are extremely helpful insight and I can use these directly.

                   Dear PhilModJunk, thank you for the "ExecuteSQL" idea! "Count" really doesn't work for this.

                   Your combined help / suggestions will help me solve this issue the way I had originally hoped it would be possible.

                   Last question to PhilModJunk, if I may: How can I write an SQL query that will count the number of times that a specific text entry (eg: AGMM or LGA) has occured in the respective database field (in this case "Auftraggeber Kürzel"), counted over all records?

                   Thank you very much again!


              • 5. Re: Complex: How to generate customized, composite invoice references ?

                     For a given record, does the value appear once in the field of that record only or could it appear multiple times in that one field of one record?

                     And keep in mind that ExecuteSQL is not required to get such a count. I just mentioned it as an example of why we need to know what version of FileMaker you are using. There are a number of ways that you can count the number of records that containe a specific value.

                     A summary report, is another example: Creating Filemaker Pro summary reports--Tutorial

                • 6. Re: Complex: How to generate customized, composite invoice references ?

                       Hi PhilModJunk and thanks for replying ! smiley

                       I'll study the example that you sent in a bit more detail tomorrow. It was too much for me to grasp at first looks.

                       But to answer your question:
                       For a given record, only one value (such as AGMM or LGA) will occur in one specific database field ("Kürzel Auftraggeber").
                       This value  will be determined for each record by means of me chosing the customer ("Auftraggeber") from a dropdown menu during the initial data entry for each record.
                       The dropdown menu is generated from a value list which in turn is created from the table "Auftraggeber".
                       So when for a specific record this value becomes LGA, I want the formula (or the SQL query) to return the number of times, LGA has occured as a value in this field in the previous records.

                       Thank you for all your help sofar and of course I'll be grateful if you have a straight shot to solve this. I'll study your example tomorrow and see whether that will solve it.

                       Best regards, Christof


                  • 7. Re: Complex: How to generate customized, composite invoice references ?

                         I'm guessing that the summary report, set up on a layout based on Auftraggeber table will produce the counts that you want.

                    • 8. Re: Complex: How to generate customized, composite invoice references ?

                           I'll work on that ! Thanks a million, Christof