filling out a field automatically depending on other fields

[constance_park]

Hi,

I have a field (invoice serial number) that I have to fill out automatically with the following criteria

last two numbers of the current year

two numbers for the type of invoice determined by a lookup table

last three numbers are sequential.

For example, I have a hardware invoice, software invoice and service invoice. The hardware invoice should be coded with 01 given that the type field is set to HW. The software invoice should be coded with 02 given that the type field is set to SW and the service invoice should be coded with 03 given that the type field is SR. This is easy enough but the last three digits are to be sequential for each type. eg:

Hardware invoices starting at number 100

Software invoices starting at number 200

Service invoices starting at 300

this is easy enough by setting up a number field for each type of invoice starting with 100 and getting filemaker to increase the value by 1 and add the result to the 2 digit year and 2 digit code. The problem is that every time a new record is entered, all these numbers are increased. I only want the number corresponding to the particular type of invoce entered to increase. Hopefully this makes sense. Any thoughts?

Here's an example of what I want the final product to be

record 1 hardware invoice with s/n 0201100

record 2 software invoice with s/n 0202100

record 3 software invoice with s/n 0201101

record 4 service invoice with s/n 0103100

Thanks in advance,

Constance

[danjacoby]

First of all, your example doesn't follow your description; check to make certain what you want. Doublecheck your calc -- it's probably inaccurate as well.

And a very strong warning: You're limiting yourself to 100 of each type of invoice, then your numbers run into the next sequence. You're going to have problems with that before you know it.

[constance_park]

Let me try this again.

Category Type is a Text Field with the three categories HW, SW and SR. Once the user selects either HW, SW or SR for this field, the calculated Invoice # should be filled in automatically by the calculation below.

I have a field called calculated Invoice # set up as a Number with the option to auto enter a calculation. The calculation is as follows:

Case(Category Type="HW" ,Right(DateToText(Date),2)&"01"&HWNUM,Category Type="SW",Right(DateToText(Date),2)&"02"&SWNUM, Category Type="SR",Right(DateToText(Date),2)&"03"&SRNUM )

The HWNUM, SWNUM and SRNUM are set up as number fields with the option of auto entering a serial number starting at 100 (this is where I made an error in my previous example). The problem is that every new record, all three numbers increase. I only want the corresponding number to increase. If I enter a hardware invoce, I want the HWNUM to increase by 1 and SWNUM and SRNUM to stay what they were... Hopefully I have explained it better this time.

Thanks,

[T-Square]

What about setting up three global fields, gHWNUM, gSWNUM, and gSRNUM that you insert into the calculation instead? Then you bump the appropriate global variable after building the serial no.

[AndrewBruno]

Using global fields to track latest numbers within the file won't work as you lose global values whenever the file is closed. Also won't work at all in a multiuser environment as global values are individaul to users.

You could use a complex script to determine the last number of each type of invoice and then increment, but this is messy and complicated.

A good general technique is to use a special parameters file with only ONE record. Create a record with a chosen text field to use as the join. In any other files (I create a join with all other files), create a field with a calculated text value of the same word. You are in effect creating many to one relationships. It is important that you do not create more than one record in this special file.

If the relationship is called Invoice|Parameters Your script would work like this :

If Invoice type =HR

Invoice number =Invoice|Paramters:HRnumber

Set Field Invoice|Parameters:HRnumber,Invoice|Parameters:HRnumber+1

If invoice type = SR, etc etc

This technique is useful for keeping any fixed data used throughout a system. A good example is the company address and phone details. Put these in the parameters file and use these fields (through the many to one relationship) on all your layouts. If anything changes, you only have to change one record, rather than having to go to every layout.

Andrew Bruno

(Now an avid saver of system files !)

[Fitch]

A quick comment on your calculation -- you could simplify it like this:

Right(DateToText(Date),2) &

Case(Category Type="HW", "01" & HWNUM, Category Type="SW", "02" & SWNUM, Category Type="SR", "03"&SRNUM )

As for the root of your problem, I suspect you could work it out using your NUM fields, but instead of auto-incrementing a serial number, have them auto-enter a calculated value, based on a self-join by Category Type, something like:

HWNUM auto enters...

Case(Category Type="HW", Max(SelfByCat::HWNUM +1))

Just a thought, not tested, YMMV.