Trapping field entry validation error (in script?) and acting on it

[mikedr]

I have two tables, matters and individuals, which are related by an individual's name.  That is, a user can select in the matter layout for a given matter any existing individual to assign to the matter, via a drop-down box value list.

 

This works great, unless the individual doesn't exist yet.  Previously I had required the user to go to the layout for individuals, enter the new individual, and then go back to the matter layout and select the newly entered individual.

 

I therefore improved this workflow by having a button in the matter layout, which when selected, permits a user via a popover to enter a new individual and his/her relevant information in the individual table.

 

This also works great, with one caveat.  Individual names are validated as unique, so no two individuals can have the same name.  In my popover, if a user enters non-unique individual name, FM throws up an error indicating this, and asks if the user wants to revert.

 

I would prefer to have my own error handling approach.  What I'm doing now, therefore, is in the popover, the user first enters the individual's name for the field in the matter.  Then, I have a script that is triggered which searches all names in the individual table for this same.  If it doesn't find any (which is going to be the case at least most of the time), then the script creates a new record in the individual table for this person, and the user is returned to the next field in the popover, which is the phone number for the newly created individual (in the individual table).

 

If the script does find the name in the individual table, the user is given an option to assign this person to the matter, to enter a new name, or to cancel completely.

 

All this works great as well -- except there's just enough of a delay when the script is doing its magic that I don't like it.  I assume that the issue is the searching the individual table for a name.

 

Therefore, it would be preferable to just trap the failure of validation.  I.e., I could have the user enter in the individual name in the individual table directly, and *if* there's an error, I could handle it.  (or, I could still have the user enter in the individual name in the matter table, and when my script tries to create a new record in teh individual table, trap the validation failure at that point)

 

Is there any way to trap a validation error?  Either in a script, or not in a script?

[LaRetta]

I have two tables, matters and individuals, which are related by an individual's name.  

 

Sorry, Mike, I can't get past the first sentence.  I was sure we all had showed you that using IDs for relationships was very important.

[Wim Decorte]

I was not part of whatever earlier threads were going on, but I have to firmly stand by what L. is saying here; if you base the relationship on names it will never work.  Each individual needs its own ID.

[comment]

Individual names are validated as unique, so no two individuals can have the same name.

 

Just continuing with the general theme: if you could be sure that an individual's name is [a] unique and unchangeable, then you could use it as the matchfield for relationships. However, in real life that's just not so.

 

IMHO, validating names as unique is bad practice, because if two individuals happen to have the same name, then that's how it is - and the user is not supposed to try and invent a unique name for the second (or third) John Smith. Certainly not in the name fields, which are then used for addressing correspondence and similar. Just imagine someone receiving a letter addressed to "John Smith (the adultery case)".

[mikedr]

OK, tough crowd!  First, I don't know what the earlier thread is, either! 

 

Second, I did indeed misspeak.  They are not related tables.  Rather, the individual name for a matter is selected via a dropdown list populated by names from the individuals table.  What I'm basically doing is a way to create a new individual record (in the individuals table) from the matter layout.

 

I apologize for the miscommunication.  I'm still a novice, and while somewhat adept at programming, am much lesser so with relational databases. 

 

In any case, I indeed fixed the issue, and apologize for having posted this in the first place.  The "Get(LastError)" function does exactly what I need it to do.  So, happy days!

 

Merry Christmas to everyone!

[LaRetta]

LOL, I didn't mean to come across as tough but on the subject, yes we must be tough.  It is the biggest mistake folks can make and we spend roughly 25% of our time correcting or encouraging those that don't know, to use meaningless key IDs.  

 

So I'm glad to hear the names are selections in drop-downs. I'm not clear what you came up with ... there are a few basic approaches to creating a related child record but as long as you're happy, I'm happy.  I have little time right now to delve further anyway.   

 

Happy holiday to you as well, Mike!   :-)

[mikedr]

It's not really creating a related record per se. Rather, I have a button that only displays if a name hasn't been selected, and which shows a popover. First field is the name field from Matters. This triggers a script, which tries to create a new record in Individuals with that name. I trap errors from setting the name in individuals, to ensure uniqueness.

In testing, the trapping error approach doesn't seem any faster than searching for existing records with the newly proposed name, but that's because (I'm assuming), right now there are only 60-odd records in the Individuals table.

I do understand the criticism regarding the names being required as unique. At this point, I'm going to wing it until the first time it becomes a problem, because it helps out a lot in so many other ways.

My solution is for a small patent office -- myself and a paralegal, the latter whom I hired just this past year. My "everything's in the brain and an Excel spreadsheet" approach wasn't working, hence going to FMP. So there is a bit more flexibility than with an enterprise level system (say). Right now my matters table is roughly 2,600 records, which represents about 15 years worth of work, and I'm hoping to be retired in another 15!

I will say, though, I created a neat little tool in FMP (via scripting and good plugins) to generate a particular legal form automatically, and my paralegal said I should sell it. I looked around, and competing solutions are either $350,000 () or $80 per generated form. When I have some free time next year, I'm going to see how hard it is to put a web site front end on this puppy and market it.

[LaRetta]

Well I would say that if you won’t listen to experts in this business (Wim, Comment), on issues so fundamentally critical to solid design, then I would not take bets on it.   

 

You will entrench yourself using names throughout your solution and will then resist the greatly-increased difficulty of switching it later not to mention the breaks in data you will incur when data begins to incorrectly relate and all invoices of a customer appears on another customer's statement - this is just the tip of the mess it can make.

 

I urge you to relate them properly.  We know the cliffs in this business ... you do not.  All we can do is tell you the cliff is there but the feet belong to you if you choose to walk over the edge anyway. 

[bruceR]

Mikedr, if you think you've run out of questions, maybe your approach will work.

 

But asking for free help, and insistently reminding us that you intend to make poor design choices so that the burden of providing you with this free help will be greater… 

 

I dunno, where do you think that leads?

[Kris M]

 When I have some free time next year, I'm going to see how hard it is to put a web site front end on this puppy and market it.

Not to be a stick in the mud this is not a novice level project. Their is alot more to consider than just the UI.

[mikedr]

Yes, the web site front end would require the assistance of a developer for certain.  I did some poking around this site and on the Internet generally, and the constraints are certainly non-trivial.  There was one thread in particular about a guy wanting to do this, although his web site would have many orders of magnitude more concurrent sessions than mine would.

With respect to have unique names (and please do understand that the names of individuals are not being used to relate two tables together -- that was an error in my original message), I do understand the issue.  However, my large corporate clients have this same issue, and the way they get around it is how I plan to if/when it pops up -- namely, adding a number to the end of the last name or first name, or somesuch additional modifier.  That is, you would have Joe Smith1 and Joe Smith2. 

[Kris M]

mikedr.. Just because someone else has done it does not mean its good practice.

[Aussie John]

If you use an ID serial number for the relationship, it is still possible to have a pull down menu with just the name. In the value list dialog select the id in the primary list and the name in the second list and choose show only the second value. If you do have persons with the same name then you can make a concatenation calculation field with some other identifier(s)

[mikedr]

If you use an ID serial number for the relationship, it is still possible to have a pull down menu with just the name. In the value list dialog select the id in the primary list and the name in the second list and choose show only the second value. If you do have persons with the same name then you can make a concatenation calculation field with some other identifier(s)

 

Thank you very much -- this is good information!!