Invoice Number

[AudioFreak]

Hello everyone,

I'm going crazy tying to get this to work and I'm sure it's soooo simple. I have a number field that is the invoice number. I would like to have a button that when selected will insert the next number(highest +1). I have tried the max function but it does not work. When I click the buttton it gives me 1 instead 1001. It appears to not be seeing the highest number in the database. What am I missing here. Thanks for the help.

[-Queue-]

Max() will only work for all records being browsed. Show All Records, then set the next number.

[AudioFreak]

Thats not it. 2 records in the database. record# 1 has 100 in the invoice field. Move to record 2 select the button it inserts 1. It should be 101. I'm missing something stupid I'm sure of it.

[Ugo DI LUCA]

Huh?

Max should work...

What is the relationship you used for this Max calc ?

A constant relationship in the Invoice File should work.

Max(ConstantRel::InvoiceN

[-Queue-]

Are you sure you're Maxing the correct field?

[Ugo DI LUCA]

Hi Queue,

Is this question adressed to Michael ?

[-Queue-]

If Michael is AudioFreak, then yes.

[AudioFreak]

Here the calc.

Insert calculated result[select,"Invoice_nu", "max(orders invoice::invoice_nu) +1"]

It only seems to change to the correct number if I'm on the record that has the highest number. Then it replaces it with the next highest. God I'm losing my mind on this one. And I'm gonna kick myself when I realize what I did.

[-Queue-]

Is Invoice_nu on the layout where the button is located? Insert calculated result will not work if it is not. Why not use Set Field though? It doesn't require the field to be present on ANY layout.

What fields does your orders invoice use to relate? If they're not constant (i.e. 1), then it may not work. If you are using the invoice number to relate, then it definitely won't work because the invoice hasn't been created yet.

[AudioFreak]

Yes the field is on the layout, in fact the button is right next to it.

[Ugo DI LUCA]

Also,

max(orders invoice::invoice_nu)....

Doesn't give many insight about what is this relationship like.

It should be a constant relationship.

If you're creating a new record and the relationship is Cust_ID::Cust_ID (i.e.), you'll surely get a 1 if this is the first invoice for that customer.

Use a global (populated by 1) and a constant indexed calculation(populated by 1 also) and create a relationship Constant (global::indexedcalc)

[AudioFreak]

Ugo,

That was my next thought on how to do this. I would just really like to know what the heck I've done wrong here.

Question: What do you mean by constant relationship? Is there more then one type of relationship? Maybe this is what I'm missing.

Michael

[-Queue-]

Contstant relationship = 1::1 will always be valid, whereas relating invoice_nu::invoice_nu will not be valid until a related invoice number exists.

Are you relating two files or simply one file to itself?

[AudioFreak]

It is a self relationship.

Relationship Name Relationship Related File

Invoice Number Invoice_nu=::Invoice_nu Orders Invoice

This may help.

[-Queue-]

Then you are relating two files, Orders and Invoice?

[Ugo DI LUCA]

It is of good practice to use in each file either :

- a global field populated by a 1

- an autoentered num field or a indexed calculation with result = 1.

Displaying in portal the fields coming from a self-constant relationship global::calculation would give the same result as if you had performed a Show All records.

Displaying in a portal the fields coming from a "cross-constant relationship" (from File A o File : would give you a complete list of all records in the related file.

This constant relationship is always valid as each field of your db has this record (=1) then can be related.

[-Queue-]

No need to use a global for a constant self-relationship though. Just relate a constant, indexed calc of 1 to itself.

[AudioFreak]

Orders invoice is one file

[Ugo DI LUCA]

Correct.

I'm using this same global field for many other needs though (visibility, flag,...) with some toggle (1,0)...

That's why I have in each field both a global and an indexed calculation.

Simplest to start with an indexed calc for both sides though...

[AudioFreak]

Ok I just added another field to the layout to show me the max number. It is only seeing the number from the current record not the found set. Ahhhhhhhhhhhh

[Ugo DI LUCA]

Michael,

Calm down and drop down the bass level

Create a calculation - indexed field called c_constant.

Enter 1 in the Calc box.

Create a relationship called Constant matching c_Constant for both the left and right side.

Change your Calculation to Max(Constant::InvoiceN

[-Queue-]

In that case, create a calculation field (I call it _ because it sorts to the bottom of field definitions and is easy to locate.) that is indexed. Then create a _::_ self-relationship, and when the button is clicked, Set Field [invoice_nu, Max(_::invoice_nu) + 1]

This should work, assuming that your invoice numbers will only increase in order, so you won't have one record with 50 as its invoice number and the following record with 43, etc. If that is the case, then you'll need to sort your relationship definition by the invoice_nu to ensure that Max gives you the largest number and not the last created number.

[AudioFreak]

RE: Calm down and drop down the bass level (surprised you remembered) :-)

When I sat down to do this I did not think it would be this difficult. I had less trouble writing a script that gave me multiple reports with user interaction.

I will try both your suggestions for the learning experience. Thanks for all your help.

Michael

[Lee Smith]

Too many chefs spoil the broth

[CobaltSky]

Lee Smith said:

Too many chefs spoil the broth

Actually Lee, if you remember to take their hats and boots off before you pop them in, they give the broth a bit of extra body!

Meanwhile, I thought that a rapid-fire sample might help Michael with his current headache...

serials.zip

[AudioFreak]

Thanks for the file Ray. After I unzip the file I can't open it. I have had this problem with other files when the file icon is white with the corner folded down(I'm on a MAC or should that be Mac ). Could you possibly e-mail me the file. Thanks so much.

Michael

[Ugo DI LUCA]

In Italy, we use to say :

"Alle prime minestre non s'ingrassa"

Still some "soups", but different meanings.

How is this soup going Michael ?

[Lee Smith]

Hi Ray,

ROTFLMAF

Hi Michael,

I just downloaded the file (Mac, OS9,1) with no problems. If you are still having problems, contact me off list and I will forward it to you Stuffed.

Lee

[Ugo DI LUCA]

Ray, after downloading your file (I was a bit scared... )...

Should I put my boots on and jump into this soup

or just keep them clean for tomorrow...

Another Italian proverb : Bisogna guardare a quello che si fa, non a quello che si dice.

[SteveB]

Ugo: Ya gotta translate them for us, cause otherwise it's just another saying on the side of the linguini box.

Steve

[AudioFreak]

Ugo,

It seems removing the boots and hats got it working. I think I was just thinking about the same thing for too long. Took a break for a day came back today just a few minutes ago and set up what you suggested and it worked today. Not sure what I was doing wrong. I do think being tag teamed by 2 people with different suggestion may have confused me a little. But it's all good works just fine. Thanks!

Michael

[Ugo DI LUCA]

Linguini Box

I think these were Spaghetti, turned linguni by a very bad chief...

You know proverbs are dificult to translate. May be Google could give some insight her...

The first proverb was saying :

"Alle prime minestre non s'ingrassa".

You don't get fat with the first soup....---> about Courage.

The second :

"Bisogna guardare a quello che si fa, non a quello che si dice. "

You need to look at what is done, not what is said

[Lee Smith]

The second :

"Bisogna guardare a quello che si fa, non a quello che si dice. "

You need to look at what is done, not what is said

Sort of like:

Actions speak louder than words

[Ugo DI LUCA]

Sort of....Yes.

or a little demo is worth a thousand words...:

[Lee Smith]

AND ---

A demo with a few words is the ultimate.

It also doesn't hurt if the reader has RTFM, and some of the fine after-market books, such as, Special Edition Using FileMaker Pro 5 by Coulombre & Price, to name just one.

Lee