Unique value validation with exceptions

[filomarino]

Hi all,

i have a problem... i want to validate a field to have a unique value, but i want to have two values that can be repeat. For example a field CATALOGID, must be unique but if i put teh value "NON CATALOGED", I want to pass the unique value validation.

Anyone can help??

Thanks

[LaRetta]

One way ... Create another table occurrence of your table (called a self-join). Join CatalogID to CatalogID using =. Add this validation (by calculation) to the CatalogID field:

CatalogID = "Non Cataloged" or IsEmpty(selfjoin::CatalogID)

If you want the field to ALWAYS have a value, change the calc to:

CatalogID = "Non Cataloged" or not IsEmpty(CatalogID) and IsEmpty(selfjoin::CatalogID)

... and uncheck 'Validate only if field has been modified.' Provide a message telling User to correct it. And uncheck 'Allow User to Override during data entry.'

I'm not particularly fond of field-level validation. And I would question using a CatalogID in this way. I hope this is not your unique serial ID that your relationships will be based upon. Users shouldn't be entering this ID at all. If you explain more of your situation, I would bet there are better ways of handling it; but I can't make suggestions with such little information to go on. It sounds more like this may be similar to a part number (book number?) IN ADDITION to a behind-the-scenes unique ID. Is this correct?

LaRetta

[filomarino]

Thanks...

I explain a little more, its not a catalogID, is exactly a INVOICE Number, but some expenses dont have invoice, just a ticket, the values to override the unique value validation are: TICKET and NOTE.

I want this validation bc some times i have duplicate invoices added to my purchases report.

I try what you told me... but i dont know where to put the validation calc... in which table? the one i created or where i introduces data?

the tables are: PURCHASES (where i enter data) and INVOICETABLE (the one i created)

if i put the validation calc in PURCHASES::INVOICES

INVOICE = "TICKET" or not Count(PURCHASES::INVOICE)

any data i enter diferent from "TICKET" gaves me my failed validation message, even if is not duplicate...

Thanks and wait for your anwser

Rodrigo

[LaRetta]

Hi Rodrigo,

Ummm, I wouldn't have non-invoices in with invoices. Too much rides on the InvoiceNumber. I would suggest letting FM handle your Invoice numbers and Users should NEVER have that control. It feels like you are mixing apples and oranges in the same table. Sometimes this is necessary but try not to mix them. If you MUST have records in which only SOME are considered invoices, then still let FM handle the process (scripted after-the-fact with a loop at day-end or month-end or at moment of invoice) but never by Users ...

I'm having a bit of trouble understanding what TICKET and NOTE represent. Can you explain a bit more?

Oh. And forget the Count() as I had originally typed that calc. I immediately realized my error and corrected it to IsEmpty().

LaRetta

[filomarino]

mmm..

i must... really is a simple solution what i making..

Im Art Director, so i have a budget and a have to purchase a lot of products, some times i purchase products with no invoices, so i only get a purchase comprobant (TICKET).

the fields of my solution are this:

-Date of purchase

-Category

-Description( of what i purchase)

-INVOICE (but some times i have not, so i put TICKET)

-subtotal (the money)

- TAX ( a little calculation of my taxes)

total (subtotal+TAX)

i need to validate INVOICE to by unique unless the value is "TICKET"

Thanks

[LaRetta]

I am still unclear on your structure but here is where your validation breaks:

if i put the validation calc in PURCHASES::INVOICES

INVOICE = "TICKET" or not Count(PURCHASES::INVOICE)

The validaton on the InvoiceNumber field should be in Invoices. And you need a table joining Invoices to itself on that InvoiceNumber. To pass validation, the calc requires the following to be true:

1) InvoiceNumber = "TICKET"

OR

2) InvoiceNumber is not empty

AND

that InvoiceNumber doesn't yet exist (tested against a copy of the same table's InvoiceNumber).

[comment]

Perhaps you need another field, 'Type' or something, that can be Invoice or Ticket or Note. Then the InvoiceNumber field can be empty when there's no invoice.

[LaRetta]

Ah. Invoice as in general information - not invoice as in business-generated Accounts Receivable invoices.

Then my above explanation should work for you. You will need to add the CatalogID = "NOTE" as an OR condition like TICKET lists.

You see? Now we're swimming in mis-named fields and confusion. It's always best to give real information to begin with. But we'll figure it out nonetheless. :wink2:

[LaRetta]

It would certainly make it simpler, Michael. As you've seen, we went to Las Angeles via London on this one (my mind was off in another direction). By eliminating Ticket and Note, FM's validation will handle it all (using Unique validation) without another TO and would be more efficient.

[filomarino]

Thanks LaRetta for your help,

finally i get it work with this

If ( INVOICE = "TICKET" ; 1; If ( INVOICE = "NOTE" ; 1 ; not Count(TABLE 2::INVOICE)))

In this way, i manage the two posibles non invoices data, TICKET and NOTE...

Thanks people!!

Rod

[LaRetta]

If you want the field to have an option to be left empty:

INVOICE = "TICKET" or INVOICE = "NOTE" or IsEmpty(TABLE2::INVOICE)

If you want to demand that SOMETHING be entered into this field (notice there are three tests):

INVOICE = "TICKET"

or

INVOICE = "NOTE"

or

not IsEmpty(INVOICE) and IsEmpty(TABLE2::INVOICE)

You don't need an If() test here nor do you need to create the 1 or 0. Validation IS a boolean test, only responding if the test fails the validation. There are 3 individual tests in that last calc - one of them must pass. Is there any reason you can't put a TYPE field which contains 3 possible values (radio buttons: Invoice, Ticket, Note)? Then only enter the Invoice Number if you have it. Your validation, as Comment pointed out, would be simpler and you wouldn't need TABLE2. It would also give you the ability to further filter and group your Purchases (which is always a good thing).

LaRetta