Complex: How to generate customized, composite invoice references ?
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:
- How can I derive a 2-digit-year code (YY) from the current year?
- 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.
- 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).
- 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!