Autoenter date + letter as serial number

[lolitafontaine]

I am creating a database that will track votes taken in meetings. There is usually more than one vote in a meeting, but only several meetings a year. I would like to set up a unique VoteID field which should be the date (without punctuation) and a sequential letter.

For example, on Jan 1, 2000 we voted on four things: death, taxes, sex, and war (um - not really, but this is just a scenario), in that order. The VoteDate field will read 1/01/2000, but I'd like the VoteID field to be automatically generated each time a new record is generated as:

Death = 1012000a

Taxes = 1012000b

Sex = 1012000c

War = 1012000d

How do I do that? Any ideas?

Thanks!

[sbg2]

1012000... is that January 1st 2000 or October 1st 2000?

What happens when you have 27 votes at 1 meeting?

IMO, you are much better off using plain old serial numbers for the ID of each record.

[aldipalo]

Oops, just noticed you are using FM6. Not sure if you can use a Let statement or Variables in that version. Never used it myself. If not, please disregard.

**************************************************

Assuming you have 3 fields:

VoteDate

Vote

VoteID /* Text field, autoenter calc, do not replace unchecked.

Let ( $Vote = Vote;

Month (VoteDate) & Day(VoteDate) & Year (VoteDate) &$Vote

)

You can have it display

0101200death

or

0101200a

or

01012001

If you add

Let ( $Vote = Vote;

Month (VoteDate) & "-"& Day(VoteDate) &"-"& Year (VoteDate) &"-"&$Vote

)

you can separate the date data so it is more readable or do anything you'd like to it.

HTH

Edited August 22, 2006 by Guest

[T-Square]

Lolita--

Listen to sbg2--create an auto enter serial number, and let calculations handle display to your users.

David

[aldipalo]

sbg2 and David:

I agree that it would be easier to just have a serial number, but, that's not the question asked.

Perhaps, she intends to use this field for reports, to tally the vote, etc. A unique serial number will not accomplish this. Since her reasoning was not given I just felt we should give her what she asked for. I also assumed ( which we know what that does) that she already has a recordID for each individual record since this field will not be unique to a record. But, perhaps Lolita can give us more info about her table structure so her question can be best answered.

[lolitafontaine]

Actually, I'm stuck in that place where the boss wants it to look a certain way. She gave me the format, and I'm trying to accomplish her wishes (she's not good at "It doesn't work that way" reasoning).

We will use this in reporting functions later (we're just developing this DB), and to have VoteID act sort of like a serial number only with more information will be helpful in the future.

sbg2 - there are never more than 4 or 5 votes at a single meeting. They never get that much done! Good point, tho.

aldipalo - I am currently working in FM6 but am about to upgrade to 8. We don't have the server software yet, tho, and this DB needs to be shareable (hence, why I was writing in 6). I will try the Let function in FM8, tho, and keep nagging IT to get FMServer online.

[LaRetta]

...where the boss wants it to look a certain way. She gave me the format, and I'm trying to accomplish her wishes.

That is always the right approach but ... HOW you accomplish it behind the scenes (as long as they get the desired result) is up to YOU. And you should not compromise your data entegrity because someone - anyone - wants something a certain way. They do NOT understand database management.

Simply ... if you make your relational keys have meaning; if you manually manipulate or set your uniqueIDs, it will bite you (and this business) BIG TIME. Leave your serials pure and create another field which concatenates into the format they wish. Reporting will be just as easy; in fact, easier. I can't make this suggestion strongly enough.

LaRetta :wink2:

[Inky Phil]

What LaRetta said......