How to generate a field by using three other fields

[emileezer]

Hi there. I am pretty new at this. What I want to do is take three different fields and combine them to make another field (a file name).

Here's what I want:

I want the creation date (which shows up as 05/01/2008), one of two text choices from a drop down menu (LK or OMI) and the automatically generated serial number to combine to look like this

0501LK1234 (in it's own field.)

I have absolutely no idea how to do this, I have never used scripts, and what I tried in calculation just didn't do anything.

Can you help? Thanks!!

emileezer

[Raybaudi]

Hi

no script is required, only a calc fied:

Riht("00"& Day( date ) ; 2 )

&

Riht("00"& Month( date ) ; 2 )

&

YourFieldWithList

&

Riht("0000"& Serial ; 4 )

[emileezer]

do i put that in a calculation field?

[Raybaudi]

yes...

P.S.: that calc will give day & month... change it to have month & day if 05/01/2008 of your example is the today date.

Also change every ";" of that calc to ",", as American FM6 is different from European version.

[Fitch]

It depends, but for now let's say yes, put it in a calc field.

Note that in the calc below, "YourFieldWithList" and "date" and "Serial" are just placeholders for your actual field names.

In the US we put the month first, so that would be:

Right("00"& Month( date ) ; 2 ) &

Right("00"& Day( date ) ; 2 ) &

YourFieldWithList &

Right("0000"& Serial ; 4 )

[emileezer]

It is kind of working (closer than I was) but it just seems to reegister the last part of the calc, and it is depositing four zeroes in front of the serial number... (00001234 is what shows up in the field)

I tried to tweak it, do things that seemed like they make sense, but it didn't really do anything. It seems like it isn't picking up the list (client) or the date (materials due)... do spaces matter?

I really appreciate your help!

Edited May 1, 2008 by Guest

[Raybaudi]

Plese, give me the real names of your 3 fields...

[emileezer]

"Job #" is the auto generated serial number

"Client" is the list

"Materials Due" is the Date, which would display today's date as 05/01/08

It seems what it is doing is just picking one of the fields to perform the calculation on, and it just happens to be the first one. When I leave one of the fields blank, it will do the other field (so if I leave "client" blank, it will calculate "Materials Due"

[Raybaudi]

Copy and paste this calc:

Right("00"& Month( Materials Due ) , 2 ) &

Right("00"& Day( Materials Due ) , 2 ) &

Client &

Right("0000" & Job # , 4 )

... and say us what is the result.

[emileezer]

the result was 00002043 … four zeroes and the serial number of 2043... i did change the ";" to commas as you suggested before.

A little test: I went in and deleted the last part of the calc, the part with the serial, and then it ran and resulted in the four zeroes and the client "0000OMI"

And then when I delete the "client" part from the calc, it runs the date correctly.

The individual sections seem to running fine, it just isn't doing all three of them together.

Here are the calculations and results:

Right("00"& Month( Materials Due ) , 2 ) &

Right("00"& Day( Materials Due ) , 2 ) &

Client &

Right("0000" & Job # , 4 )

RESULT: 00002043

Right("00"& Month( Materials Due ) , 2 ) &

Right("00"& Day( Materials Due ) , 2 ) &

Client

RESULT: 0000OMI

Right("00"& Month( Materials Due ) , 2 ) &

Right("00"& Day( Materials Due ) , 2 )

RESULT: 0501

[emileezer]

I GOT IT TO WORK!!!

The formula was fine, after about the 80th time I copied and pasted, it worked... I don't know what the difference was, but THANK YOU VERY MUCH!

[Raybaudi]

I think that you had pasted that calculation into the option of auto-enter a calc into a normal text field, not a calculated one.

That way is fine with FM from 7 to 9, but isn't with FM6.