Jump to content
Sign in to follow this  
_Kimmie_

Need a Trigger for Invoice Number

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.