Hi All,

I am creating a DB for a magazine. The DB consists of 12 months per year for 4 years. A company may have purchased certain issues from mid year to mid year on the same contract. I need to generate a unique invoice number per contract (companies can have multiple contracts in one year) and I think if I use the contract date as my trigger I can use a calculation. The problem is I have the dates for each year as one repeating field per year to input contract date. How would I write the calcualtion to look for a group of specific dates, per record in a repeating field to generate a unique invoice number? I am self taught on FM, I have been working with it for a few years. Any help would be appreciated!

Thank you for your time.

Kim

Hi Kimmie:

Welcome to the forum. IMHO, I would not use a repeating field for a date or invoice number. The beauty of FM is that it is a relational database. I would have a Contact table and a Contracts table as that would be a one (Contact) to Many (Contracts) relationship. Set your relationship as Contact_Table::ContactID = Contract_Table::fk_ContactID.

Then if you want a unique and identifiable invoice number using date I would set up my date field as a timestamp field and concatenate the contactID with the timestamp field. So you would set your calculation as:

fk_ContactID & Datefield.

You should get something like 100105162007124635

ContactID = 1001

Date = 05162007

Time = 12:46:35

Unless you are selling thousands of contracts an hour this will give you a unique invoice number. You could also add, or use in place of timestamp, the ContractID number which is unique to every record. So you would have 1001006

ContactID = 1001

ContractID = 006

HTH

I apppreciate your help and thank you for welcoming me to the forum! Unfortunately, the DB is already built and my time is up to re-create the entire DB. I was hoping there was a way to query the dates? Thanks again!

I'm sorry, you said you were creating a db so I thought you were still in the design stages. Take a look at this post which might steer you in the right direction. Since I don't use repeating fields in my apps I cannot suggest the correct calc.

http://fmforums.com/forum/showtopic.php?tid/75384/post/75469/hl/%22repeating+field+calculations%22/

hth