Dynamic Field

[DALLASTXPAT]

Lets say I have 4 fields:

1: Job Title - Which is a text field with a value list of positions we offer.

2: Hour Type - Which is a calculation based on job title.

If(Job Title = "Manager"; "Salary"; "Hourly")

3: Amount Per Hour

4: Amount Per Year

Below these fields 1 calculations field named "Yearly Amount" looks at field three and does a calculation if true and does another if false. Everything is working great except I want to make this thing dummy proof.

My goal is to have either field 3 or 4 show up on the layout or have the opposite blocked out (whichever is easiest) and show one or the other.

Since I may have confused you here is what I want in actual data.

Somebody selects "Manager" as the Job Title. Automatically the Hour type becomes salary. Since the hour type is salary I want the field of "Amount Per Year" to show up. If that is false I want to see the field Amount per Hour or nothing.

Any input would be appreciated. I am very new to this but am learning.

[-Queue-]

Create a calculation field equal to

Case( Job Title = "manager"; Amount Per Year; Amount Per Hour )

and put that on your layout instead of either of the 'Amount' fields.

[DALLASTXPAT]

I'm sorry I'm not following.

I still have the fields Amount Per Year and Amount Per Hour defined in the database but no longer in the layout. I created a new field named Rate as a number and auto enter the calculation you gave me. Gave it a shot and nothing.

What am I doing wrong?

I told you I was new to this.

[-Queue-]

Auto-enter won't work unless you are creating a new record, modify a record where the field is currently empty, or deselect the 'do not replace existing value' option and modify the record. It would be easiest to make it a simple calculation field, not a number field with an auto-enter calc. But if you want it to be auto-enter, you'll need to modify all existing records so that it updates.

[DALLASTXPAT]

Now I am probably getting on your nerves.

I changed the "Rate" field back to a calculation field containg the formula you gave me. How do I put the amount so the calculations below work. I can't enter anything else numeric and am now lost again. I hope I am making sense. If you want I can email you the file.

[DALLASTXPAT]

I attached the file I think

BluffsCopy.fp7.zip

[-Queue-]

I'm sorry, I don't think I understand your question. Can you elaborate?

[DALLASTXPAT]

Basically ignore everything above Job Title in the employee info layout. I have a drop down of job title with various positions we offer. Once the position is selected I have a calculation that determines the type of hours this person will get paid for. Either Salary or Hourly. Then you see the white space in between Hour Type and Amount Per Hour. I want that to have either the amount per hour field there or the amount per year field there. Currently I have those 2 there as static boxes. My goal is to have neither of them there until called upon by a script.

My assumption to solve this problem is to have a uique field that can change properties. E.g Change its field name from "Unique-Field" to Amount per hour and also have the field label change in accordance. This field will allow me to type in a numeric amount and the calculations below will happen. And just so there can't be any errors limit the number of characters to 2 before the decimal. $15.00 or 3 if you need to include the $sign.

This problem sounds like something petty but the people in my industry aren't always the greatest with this type of detail. So the less they can play with the better.

Hopefully this is elaborate enough.

Thanks for your help so far.

[-Queue-]

Okay, I see now. You can do this without a script.

Create a new number field called Rate. (You could change one of your existing 'Amount Per' fields, but this way may make it less confusing for you.) Set it with a validation calculation of

Hour Type = "salary" or Length(Left( Rate; Position( Rate & "."; "."; 0; 1 ) - 1 )) < 3

Deselect 'validate only if field has been modified', click OK, select 'Display custom message' and enter the message you want your users to see when the validation fails, then deselect 'allow user to override.'

Then, change your Yearly Amount calculation to

Rate * Case( Hour Type = "hourly"; 2080; 1 )

And create a calculation field for your label of

"Amount Per " & Case( Job Title = "manager"; "Year"; "Hour" )

and put this next to your Rate field on your layout. You can highlight the current label next to the Amount Per Hour field, and type <<YourLabelCalcName>> into it, then change the Amount Per Hour field to your Rate field.

You can then delete your original 'Amount Per' fields and your script.

No $ is necessary, since the field is formatted to display it already. You can include this in your validation message if you like, informing the user to remove the $, which is a good idea not to use anyway, because it can screw up certain exports and other types of databases.

[DALLASTXPAT]

I can follow instructions. I really need to learn functions. They make sense when I read it but knowing when to use it is another thing.

Thanks for your help,

IT WORKS

Now I am going to drink.