if statement not perfect, why?

[LML]

Hello all,

Help please. I have a contact list db I'm working on. In it, there is a section to enter first a physical address for a company, and then second the mailing address for the company.

I made question of "Is the physical address the same as the mailing address?" and a drop down list with "yes" "no" ---calling this field autofill mail address If it is yes, the address entered in the fields for physical address are pasted into the fields for mailing address.

The script works fine if the answer is "Yes".

However, when I get to the "No" I want people to be able to type into these mailing address fields. When I test "No", it keeps telling me 'this field is not modifiable' I have looked at settings for the fields, and I can't see what I have to change to make it possible for people to enter text in the fields upon a "no" answer.

Help?

Thanks,

Lynn

PS: Temporarily, I am using the following calculation, even though I know it has to say something other than "test type" at the end.

Postal Street Address = If(autofill mail address = "Yes", Phys Street Address, "test type")

PPS: Is there a way to make a radio button for yes and no instead of a drop down list?

[keshalyi]

One good way to do this would be to make two seperate fields - Mailing Address Calculation and Mailing Address Manual. Have the calculation version with logic that states that if your yes/no button = yes, then set it to the Physical Address, if not, set it to Mailing Address Manual. Then, In the layout, put the field for Mailing Address Manual underneath the field for Mailing Address Calculation. Then define the Mailing address calculation field on the layout as a button, to run a script that, if your button equals no, takes them to the Mailing Address Manual field (thus bringing itt to front). IF its yes, it can return a message that tells them that the field is unalterable unless they change their yes/no setting. That way, what ends up happening, is it looks like one field to the user, but its actually two.

As for the radio buttons, there should be another choice in the list that defaults to 'Pop-up list', that reads Radio buttons. Although, I'll warn you, I don't like the default layout for the radio buttons, they're kinda ugly...

[bobsmith]

It appears that maybe your mailing address is a calculated field which states:

If Button = "Yes" mailing address = physical address otherwise "please type". If this is true you can not enter date in the maling address field (you can not enter data into a calculated field). You will need to somehow script to enter the data. If button = "Yes" set field to physical address. This method would require a button to run the script. The other option would be seperate fields as the previous reply stated.

[LML]

Thanks so much for that good answer. It took me a while to figure out how to write a script, but I did it. (My first FM Script!)

Also, I never knew about layering fields like that. Neat stuff. Makes sense.

I have one more question on this. If they check "yes" then try to modify the field, they are unable (that is good) and then a standard error message shows up saying 'this field is not modifiable' ..... how do I get that to come up as my own error message saying 'check no if you wish to modify this address' ?

Thanks again,

Lynn

[The Bridge]

Here's an option involving Lookups:

Define fields:

Primary_Key (text, unique)

Physical_Address (text)

Mailing_Address (text, Look-up)

Same_Addresses (number)

Same_Address_Constant (text, indexed) = Primary_Key & "1"

Same_Address_Key (text, indexed) = Primary_Key & Same_Addresses

Create a value list:

vl_1: "1"

Define a self-join relationship matching Same_Address_Key to Same_Address_Constant

Set Mailing_Address to Look-up Physical_Address based on self-join relationship above. Specify Do Not Copy if Empty and If No Match Do Not Copy.

Format Same_Addresses with a checkbox based on valuelist vl_1.

When the user checks off Same_Addresses, it will trigger Mailing_Address to look-up the value in Physical_Address. The user will still be able to edit Mailing_Address.

Hope this helps!

[LML]

Peter, that is very interesting. I had to read my FM manual to understand what a Lookup is first, and now I see where you are going.

Is there an advantage to running it this way, rather than the other way (based on keshalyi's suggestion)? I was able to get that method to work.

If you could help me understand the pros and cons of each method that would be really helpful.

Thanks,

Lynn

[Kundinger]

Hi Lynn,

I do a very similar type of system for my various clients' solutions. Over a period of many years... with a lot of trial & error... I have come up with a solution for implementing the 'auto address' function.

Here is my idea:

First you have your "Street Address" fields:

- addressSTREET (text)

- citySTREET (text)

- stateSTREET (text)

- zipcodeSTREET (text)

Next you have your "Mailing Address" fields:

- addressMAILING (text) with "Auto Enter" option "Calc" = 'addressSTREET'

- cityMAILING (text) with "Auto Enter" option "Calc" = 'citySTREET'

- stateMAILING (text) with "Auto Enter" option "Calc" = 'stateSTREET'

- zipcodeMAILING (text) with "Auto Enter" option "Calc" = 'zipcodeSTREET'

How this works... when the user enters data into the "Street Address" fields, the data automatically enters into the "Mailing Address" fields. This ONLY happens the first time the user enters data into the "Street Address" fields. Any additional changes to the "Street Address" fields will not be updated in the "Mailing Address" fields. Now, the user has the option of making any necessary changes in the "Mailing Address" fields. This works very well... my clients like it because data accuracy is maintained when the Shipping and Mailing addresses are the same.

Some other ideas:

-- I use a second address line for apartment, suite, room, office, etc...

-- I create a "Shipping Label" field and a "Mailing Label" field to show the user what they will look like.

-- I use special calculations for the labels... just in case a user 'empties' all the "Street Address" fields or all the "Mailing Address" fields.

-- I use a different ZIP code for shipping than is used for mailing. The USPS uses ZIP 5+4+2+1 & optional 4. UPS & FedEx use Zip 5.

-- I implement a USPostalService coding function to create the appropriate USPS delivery code for use with a barcode.

-- I use a barcode font to put the appropriate USPS delivery code on the labels.

Just a few ideas... I hope they help!

Bob Kundinger

[bobsmith]

I like your solution, I can use this in one of my applications. This is the reason this forum is so great, you see from several different angles.

[Kundinger]

Hi Bob,

I'm creating a little sample file right now... Anyone interested... just e-mail me and I send it out.

Thanx,

Bob Kundinger

[email protected]

[The Bridge]

Well, as mentioned elsewhere in this thread there are often many different solutions to the same problem. I'd say work with solutions that you are comfortable with until you are ready to move on to the next level. I guess if my look-up solution has an advantage, it is that you only need the two address fields, saving yourself one calculated field. I suggest looking at Bob's auto-enter solution as well. It is pretty much the same as the Look-up solution I offer except that the Look-up solution will copy over the Physical Address every time the "Same_Address" field is checked.

Again, whatever works best for you!

[LML]

Thanks Bob & Everyone else.

This has been so helpful. All of your solutions are really innovative. For this time around, I'm using the solution from keshalyi. It does exactly what I need it to do, it's written in already and I like the fact that I can toggle yes after a no and get an update to the mailing records.

Bob, you also posted some great suggestions (shown below) .. and I'm incorporating these into my db as well. Didn't think about some of these until you mentioned them.

You'll see new questions from me on different topics in a few minutes!

best wishes everyone,

Lynn

Bob said:

Some other ideas:

-- I use a second address line for apartment, suite, room, office, etc...

-- I create a "Shipping Label" field and a "Mailing Label" field to show the user what they will look like.

-- I use special calculations for the labels... just in case a user 'empties' all the "Street Address" fields or all the "Mailing Address" fields.

-- I use a different ZIP code for shipping than is used for mailing. The USPS uses ZIP 5+4+2+1 & optional 4. UPS & FedEx use Zip 5.

-- I implement a USPostalService coding function to create the appropriate USPS delivery code for use with a barcode.

-- I use a barcode font to put the appropriate USPS delivery code on the labels.

Just a few ideas... I hope they help!

[color:"#666666"]

[Vaughan]

I try to keep things simple: no radio buttons or check boxes.

Two text fields "delivery address" and "post address entry" and a calculation field "post address". The calculation field is the one used on invoices, envelopes etc.

The calculation's logic is simple: if there is an entry in the "post address" field than use it, otherwise use the "delivery address" entry.

If (IsEmpty(post address entry), delivery address, post address entry)

[keshalyi]

Its a tradeoff, though, because while cutting out the radio button makes it simpler code, it gives the user a bunch of fields that they may or may not need, and that they don't know what to do with. Of course, that's what training is for, but still - God help me, I can't count the number of times when I've had users fill in boxes, wasting time and possibly compromising data, just because the mentality (and to be fair its a somewhat valid mentality) is to fill out forms completely. We've all been doing it since Elementary school, and leaving fields blank can make people nervous. So, in situations like this one, I like to do the little bit extra to hide or deactivate the fields they don't enter into.