Number records

[S Molly T]

Need some help with what seems like it should be simple.

Would like to increment a number in a table based on the value of another field.

If contractnumber = contractnumber

then contractcount is incremented

What I am currently doing (which doesn't work entirely) is a self-join calculation that uses Count.

Count ( sj_invoiceCount::contractNumber)

This works for the most part but does not place a value (the number 1) in the first record.

The results I am looking for are in records 1-10 but what actually happens is shown in records 11-15.

invoicenumbers.fp7.zip

[Vaughan]

Please re-state your business requirements, in english please. Don't describe what you have tried to implement already, describe what you want to happen and why.

[2ninerniner2]

I see exactly what you are trying to accomplish and it looks as though all you have to do is adjust your calc to set the initial match to "1", as it is shown in the first 10 rows, and then increment from that point. In other words, adjust the starting point. As I am far from a "pro" in this area, that is easier said than done so I can't really provide a sample ... but I know what you are trying to do :)

[comment]

The reason why you need this is crucial here, because the answers will be very different if the numbering is done ad hoc for the found set of a report, or if it needs to be based on all records in the table (which then raises a question of adding/deleting/modifying records).

[S Molly T]

I am simply trying to number each record based on the value of another field.

Each time a record is created the field contractCount should check to see if the value in the field contractNumber already exists. If the value in contractNumber exists, then the value in contractCount is incremented by one.

The goal is to apply a serial number to each record that has the same contractNumber and then start over for each new/different contractNumber.

First time a record is created with a contractNumber of 88 the value of contractCount would be 1

Second time a record is created with a contractNumber of 88 the value of contractCount would be 2 but the the value of contractCount in the first record stays 1

Third time a record is created with a contractNumber of 88 the value of contractCount would be 3 but the the value of contractCount in the first record stays 1 and the value of the second record stays 2

When a new contract number is then entered the process starts all over but the existing data stays. Like a serial number that resets each time a new (unique) contract number is entered.

The example above is what I am trying to accomplish.

[comment]

I am simply trying to number each record based on the value of another field.

Not really - you are trying to number each record based on the value of another record, not another field. I am afraid there is nothing simple about this - see:

http://fmforums.com/forum/topic/73473-serial-increment-a-child-record/page__view__findpost__p__347655

[S Molly T]

Didn't think it would be so complicated. The ultimate goal is to generate an invoice number. Each time we invoice a client right now we have to do a find in the database for how many invoices we have sent that client for that particular contract number and then manually create the invoice number by concatenating the found count number (xN) with the contract number (cNum). So the formula is cNum & xN+1

For example contract number 2358 has received 4 invoices to date so when we send out the next invoice the invoice number will be 235805.

What I was trying to do was create an invoiceNumber table that would handle numbering the invoices and then store that invoice number (as well as date, amount and other pertinent information).

If there is a better way to accomplish this, I am more than willing to consider an alternate approach.

[comment]

Why don't you simply number the invoices sequentially? I mean, who cares if it happens to be the third or the seventh invoice for the contract? In any case, you can always see all the contract's invoices in a portal.

[Vaughan]

Didn't think it would be so complicated. The ultimate goal is to generate an invoice number. Each time we invoice a client right now we have to do a find in the database for how many invoices we have sent that client for that particular contract number and then manually create the invoice number by concatenating the found count number (xN) with the contract number (cNum). So the formula is cNum & xN+1

For example contract number 2358 has received 4 invoices to date so when we send out the next invoice the invoice number will be 235805.

What I was trying to do was create an invoiceNumber table that would handle numbering the invoices and then store that invoice number (as well as date, amount and other pertinent information).

If there is a better way to accomplish this, I am more than willing to consider an alternate approach.

No need for a separate table: each Contract needs to know how many invoices it has been sent, so the field should be in that table.

All the provisos from the linked thread still apply.