_Kimmie_ Posted May 16, 2007 Share Posted May 16, 2007 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 Link to comment Share on other sites More sharing options...
aldipalo Posted May 16, 2007 Share Posted May 16, 2007 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 Link to comment Share on other sites More sharing options...
_Kimmie_ Posted May 16, 2007 Author Share Posted May 16, 2007 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! Link to comment Share on other sites More sharing options...
aldipalo Posted May 16, 2007 Share Posted May 16, 2007 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6188 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now