creating special invoice number - some help needed

[giantstep]

Hi there all

I have filemaker database that I need to make some changes to, but although I've spent a couple of hours trying, I'm failing at every turn. Not sure if it's OK to post asking for specific help with a calculation. Hope so ...

What I would like to do is change the "invoice number" from being incremental, as it is currently, to a calculated field. I would like to do something like:

[mm][yy][incremental]

so, for today this would read something like:

0404005 and next invoice would be 0404006

when the next month comes around, the "incremental" element would be reset and process starts again, so first invoice for new month would be:

0504001

I've tried to do this with a calculation field and 2 hidden fields (text) to store the values for month and year, but I'm not getting anywhere. Its many years since I used Filemaker for creating calculations, so any help with this would be appreciated

FileMaker Version: 5

Platform: Mac OS 9

[CyborgSam]

Create a number field that has auto-entered serial #s, I'll call it Serial#. Here's the calculation:

Right("0" & NumToText(Month(Status(CurrentDate))), 2) &

Right(NumToText(Year(Status(CurrentDate))), 2) &

Right("0000" & NumToText(Serial#), 4)

The field Serial# does not have to appear on the layout.

[giantstep]

Thanks for this, but will this also reset the field "Serial" when the new month comes around? The reason I need this is I don't want people figuring how many clients we have - ie if you get invoice first month with number "12" and next month with number "24" its easy to figure this out

[Michael Myett]

I'm sorry I don't have a solution for you, but would like to point out it is very likely the invoice number is the unique record identifier in your invoice file and therefore involved in relationships with other file(s).

Be careful before changing these values in existing records.

[giantstep]

hi there. Thanks for this comment. I've duplicated everything over to make sure any mistakes can be rectified, but this wasn't somethign I'd thought about - appreciate the "heads up"

I tried the script above, but couldn't seem to get it to work. It came up with an error saying that the field was not known, although I'd created it. Any suggestions?

[Michael Myett]

That formula should work okay. Did you name your new field serial#? If not that would prompt "field not known" error.

Also, you only need to use

Right("0" & NumToText(Month(Status(CurrentDate))), 2) &

Right(NumToText(Year(Status(CurrentDate))), 2) &

Right("0000" & Serial#, 4)

as the calc, just make serial# a text field (auto-enter serial).

[CyborgSam]

Michael made two good points:

1) The Serial# calc I defined WILL change every month.

2) This can screw-up relationships big time.

I suggest defining the usual "Record.ID" number field that is autofilled with a serial # and is prohibited from being modified. This is your guaranteed unique primary key. This type of key is used more than any other.

Then define the variable key, use a better name than my poor chocie of "Serial#", something like: k.mm.yy.Record.ID. The name should describe what it does, "Serial#" implies it's just a serial #.

[RAJESH]

dear friend can u pls explain me how this will restart the serial number in beginning of each month?

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[CyborgSam]

Rajesh->

Welcome to FileMaker Forums!

The calculation is:

Right("0" & NumToText(Month(Status(CurrentDate))), 2) &

Right(NumToText(Year(Status(CurrentDate))), 2) &

Right("0000" & Serial#, 4)

Since the calculation contains CurrentDate, its value is based on the current date. Since we're using the month and year, its value change every month.

Since the calculation is a key, if it is the related file's key it will need to be indexed, which means it cannot be an unstored calculation (which always refreshes). When the key is used in this way, it may need to be manually refreshed to ensure the relationship works properly. I'm not sure off the best way to do this in this situation (it's late at night...), a search of the forums will show various methods.

Sam

[Mike D.]

What you are trying to do is better done with a script and the calculation fields previously discussed here. In order to reset the increment number requires a self join/relationship based on the month & year combination.

Define a field called MonthYear that would use most of the calculation described by CyborgSam.

MonthYear = Right("0" & NumToText(Month(Status(CurrentDate))), 2) & Right(NumToText(Year(Status(CurrentDate))), 2)

Create a self relationship based on the MonthYear field.

Then have a script to create your new invoices that would include the following step.

Set Field[invoiceNumber, Max(SelfRelationship::MonthYear) +1]

I highly recommend not using this number as a key in any relationship. The number you are generating here should be for display purposes only.

HTH, Mike

[-Queue-]

I don't think the Max( ) is necessary, nor is a script needed. This is close to what I described here yesterday.

[Mike D.]

Queue,

Yep, I agree what you are suggesting would work also. Just a matter of preference for which method to use.

Thanks, Mike

[bruceR]

What I would like to do is change the "invoice number" from being incremental, as it is currently, to a calculated field. I would like to do something like:

[mm][yy][incremental]getting anywhere. Its many years since I used Filemaker for creating calculations, so any help with this would be appreciated

First of all be aware that it is pretty much rule number 1 in relational database design that a key field value shall have NO meaning. Because if it has meaning, you're going to change it and disrupt relationships. But you can make a DISPLAY invoice number that does what you want. Just don't use it as the basis for any relationships.