Calculation Validation

[rhakka]

Hello all, I'm new around here, I've been messing around with FMP for quite some time, I even have a "professional" solution out there

but there is still so much I don't know. Like this, and I'm sure I'm not the first person to want to do this:

I am making a separate Addresses file so our Contacts file will access it via portal. Our Contacts frequently turn into Customers, so they may have a Billing Address, a Shipping Address, and a Large Shipment address, or even more, so allowing an open ended number of addresses is necessary.

I need to flag the Primary addresses of each type: billing, shipping, large shipment. There will be many of each in the database, but there should only be at most, one of each per related contact. They are related by ContactCode, a unique value in Contacts.

My problem is making sure that there are not multiple "primary billing" addresses (or primary shipping etc, but i'll just refer to billing now for reference ease). Primary billing status is user indicated by a radio button, "yes" or "no".

What I wanted to do was have a calculation field concatenate the customer code with a value based on that radio button, "ccode & "bill" if yes is chosen. If "no" was chosen, it would concatenate the ccode with the unique record ID of the address.

Then, I need to make sure that value is unique. However there is no "IsUnique" function for calculations. Autoenter doesn't seem to want to evaluate after the fact, and this is a designation that wil change as people move around and such, so creating another text field to autoenter the calculation result and use its own unique validation doesn't seem to work. Also, a self-related lookup is similiarly handicapped by the need for a "relookup" to evaluate.

Finally, I realize I can write a script to check this, but then, how to trigger it? Plus it's just messy and silly. There should be a way to do this with field definitions.

If not, anyone know of a plugin that adds an "IsUnique" function or allows you to set a "unique" validation on a calculation field? Can't find one on the filemaker website. I know some C++ but it's going to take me a lot of work to get up to speed to write one myself.

Or, perhaps I'm just missing something completely obvious. It's happened

Thanks in advance for any help you can give, and long live FMP!! (oh, I'm using FMP5 for the Mac btw)

[rhakka]

slick enough, thanks!

I do actually want to be able to display just billing and just shipping addresses where appropriate, however, the & "bill" designation only happens when the record is flagged as a primary billing address. So if I prevent that from being able to happen more than once, I'm all set there too.

Still seems like there should be an IsUnique function though ah well.

[ July 13, 2001: Message edited by: rhakka ]

[LiveOak]

Another way to select a billing address is to create a field and a relationship in the customer file. The field hold the record number of a record in the address file and is used on the left side of a relationship with the field record number in the address file. To set a billing address, you just click a button on the portal row of the portal that shows all of the customer's addresses, the button copies the related record number to the field in the customer and established a relationship to select the billing address (or some variation).

-bd

[Chuck]

I recently wrote an FAQ for validating for the uniqueness of calculations. Check out the FAQ section for how to do this.

Chuck

[LiveOak]

I'm not sure if you want to be able to display just the billing address in the Customer file, but I'll assume that you will display all the addresses in a portal based upon a relationship by CustomerID. If this is true, you don't need a relationship combining & "bill" with the index just to check for duplicate billing addresses.

To check for billing address duplicates in the Address file, create a self relationship with CustomerID matching CustomerID. Create a flag field "BillingAddr?" which can be empty or "1" (number field). Create a "BillingCheck" field defined as:

BillingCheck (calculation, number) = Sum(SelfByCustomerID::BillingAddr?)

This adds up all the BillingAddr? flag fields for a given customer. If more than one billing address flag is set, this will total to more than one. Set the BillingAddr? field with a script that first checks the value of BillingCheck, if it's already one, a billing address already exists and it won't set the flag for the current record.

-bd