Really Struggling with Field Validation

[dmontano]

This is complicated for me - but I have really tried to think this through. I am obviously lacking the knowledge of one or more fundamental techniques to accomplish what I am trying to do. I will try to do my best to describe the problem.

In one table…

3 simple text fields for data entry:

given_name

middle_name

family_name

1 calculation field (stored):

uses the List ( ) function - the three fields above are the parameters

1 text field for data selection using a drop-down list:

Created a self-join and value-list based on self-join to display the results of the List ( ) function just above.

Up to this point - I can get it to work to a point. I see the results of the List ( ) function - except for the incredible difficult problem of validation and record creation sequences that prevent this from working smoothly.

The problems I have encountered are:

1. The value list will not display the results of the related list function - until the record is script triggered with "record commit (skip validation)" as I have field validation turned on for the "data selection" field within the same table.

2. I need validation to make sure the "data selection" field is not empty.

Seems like a conundrum. I have to disable validation to get the list to "show" and become selectable values; but I need the validation turned back "on" so to speak to check the field for empty or not.

What are my options?

Thanks in advance.

[bcooney]

Maybe it's because I've not had my second cup of coffee, but I can't figure out what it is you're trying to do? Some sort of "avoid" duplicates technique?

[Keith LaMarre]

I too am having a difficult time trying to ascertain what it is you're doing - and I have had 2 cups of coffee already!

However, it seems to me that the field validation for Not Empty should be on the three data entry fields - not the list field... This will always have a value as long as the other three have one...

[comment]

Try triggering this script on entering the field:

If [ Get ( RecordOpenState ) = 1 ] 

Commit Records/Requests [ Skip data entry validation ] 

Go to Field [ Table::SelectedName ] 

Open Record/Request 

End If 

---

P.S. I don't drink coffee...

[dmontano]

Here is a sample file I threw together, along with Comment's suggested script. Not sure if I created the script as directed, nor if the script trigger is attached to the correct field. Any confirmation / clarification on this would be helpful.

On first trial, I kept getting validation error and became "stuck". I noticed field validation for "person_name_preferred" was configured as: "Always", "Not Empty".

So… I changed field validation for "person_name_preferred" to: "Only During Data Entry", "Not Empty". That seemed to get me closer to what I need.

If I can get the details of this sample file functioning as I would like, are there any "gotchas" using this method? If there are any "gothcas" - what are they? What method is suggested for this sort of functionality?

Thanks again in advance.

self_join_select_name_validate.fp7.zip

[comment]

I meant:

Commit&Validate.zip

[comment]

P.S. The "gotcha" here (if you can call it that) is that a new record is committed as soon as you enter the PreferredName field. This means you lose the opportunity to revert (i.e. not to create the new record after all).

[dmontano]

Hi Comment,

Thanks! This seems to work as I'd hope. Can not thank you enough. I will try to implement this in the real file and see what other snags I run into. I have had plenty of issues like this that appear to be small and trivial that prevent me from moving forward. If the "gotcha" is that I lose the ability to revert a record, is there another method that effectively does what I am trying to do that is more commonplace AND preserve the ability to revert? I would find it hard to believe that what I am trying to do is out of the ordinary. What am I missing?

I am trying to think of what I want to do:

1. Provide a list of "allowable values" (It just happens that they reside in the same "soon-to-be" record)

2. Enforce the selection of one of the "allowable values"

If I am using a self-join to show the related allowable values, is there another way to do the same? The values are not showing up until the record is committed - I think. If so, I can learn that as a "rule" going forward.

If I am using a self-join, do I take that as a hint that a second table is needed: "Name". Name would hold as many names as a person could have. But in order for the list of allowable names to display in a value list in Person, I suspect I would have to commit each Name record, which leads me to believe the Person record would have to commit as well. If that is the case - I would be in the same position I find myself in now.

So, to beat this thing some more - is this where the use of global fields and a utility table come into play?

[comment]

The basic issue here is that you want a "private" value list - and this requires a relationship. OTOH, a self-join will not work until the record is committed for the first time.

As for alternatives, there are too many to discuss at once. For example, you could use a custom value list of "FirstName¶LastName¶Nickname" to "point" at the real value.

do I take that as a hint that a second table is needed: "Name". Name would hold as many names as a person could have.

That's a good question - but asked for the wrong reason. The real question is if the data model requires a related table of Names - or does it dictate that they should remain in the Contacts table. The UI issues will have to be solved AFTER this question is answered.

in order for the list of allowable names to display in a value list in Person, I suspect I would have to commit each Name record

Why don't you try it - I think you'll be surprised.

[dmontano]

Hi Keith,

Since the validation requires a selection to be made in the "Preferred Name" field, and the values presented in "Preferred Name" are derived from the three Name fields - I wanted to start there. Don't know why - but that is what I did. User MUST select a name from the list - but I could not get the list to appear until a commit was somehow performed.

I needed the record to commit to show the allowable related values in the list without a value being selected in Preferred Name - then I needed a mechanism that would validate that Preferred Name has a value selected before allowing the record to be added into the system.

By no means am I out of the woods. Not sure how to implement the validation and dialogs for what seems to be a simple "create" a record for a person.

[dmontano]

Hi Comment,

I do need a table of Names. I also need to store the history of names of a person. Name is not easy. I would like to use date to act as the "active", "retired" mechanism - but that requires a Name table. I am all for it - just can not build it due to my level.

I have been able to build (in my opinion) some interesting name "compilers" and "decompilers" but they were done in the context of the name components being in 1 record within person and coming together into various composites via calc. This was possible for me to do because of the fact that the pieces were in one record. It was not easy, but the thought of replicating the same functionality with a separate table of Names is beyond my capability right now. So, I thought I would focus on how to get the final "composite" Name selection figured out.

I can "see" the logical data model for this but like many other things, building it FileMaker has been a huge challenge for me.

[dmontano]

Can you elaborate on this technique?

I have no idea how you would dynamically return the values in those fields in a custom value list nor point to the real values.

As for alternatives, there are too many to discuss at once. For example, you could use a custom value list of "FirstName¶LastName¶Nickname" to "point" at the real value.

[comment]

I have no idea how you would dynamically return the values in those fields in a custom value list nor point to the real values.

The custom value list is NOT dynamic. It is the same for ALL records. To point to the real value, you'd use a calculation such as =

Case (

SelectedName = "FirstName" ; FirstName ;

...

)

[dmontano]

I am going to set this response aside for a moment…

The custom value list is NOT dynamic. It is the same for ALL records. To point to the real value, you'd use a calculation such as =

Case (

SelectedName = "FirstName" ; FirstName ;

...

)

Because…

this was very interesting indeed:

in order for the list of allowable names to display in a value list in Person, I suspect I would have to commit each Name record

Why don't you try it - I think you'll be surprised.

I stayed up until 3 AM working on this. Here's where the choice of a data model comes into play - significantly in my opinion. The two table configuration "seems" to allow me to do what I want without any script triggers and allows me to validate as well. I like this because it appears that it is NOT carrying baggage the previous self-join attempt brings in. I am dead tired right now and have not tested fully so I may have to retract this statement. This is exactly the help I have been looking for - so thank you very much.

However, the Person / Name table model poses some obstacles as well, given:

A person has a "full name" of: John Robert Smith

This would require three records in the related Names table:

Name record 1 = John ; Person record ID 1

Name record 2 = Robert ; Person record ID 1

Name record 3 = Smith ; Person record ID 1

Name record 4 = JR ; Person record ID 1

So far, so good. I can create each of these Name "components" and assign each a Name Type (Given, Middle, Family, etc.) in the portal.

How do I get those three "Name Component" records to concatenate to represent the "Full Name"? One method may be to put the "full name" in one Name record. But this poses problems as well - as the Name Components really need to be identified - Given, Family, Nickname, etc.

It is clear there are a variety of compositions and component uses in regards to Name:

1. Use "John Smith" as the "System of Record" for the database (Composite use)

2. Use "John" in a merge letter (Component use)

3. Use "John Robert Smith" in legal correspondance AND billing purposes (Composite use)

4. Use "JR" when placing a phone call (Component use)

Name table captures and stores the "component" Name(s) a Person may have. A Person needs to have their Name components "composed" into various concatenation strings based on the Person's preference and also the Enterprise's business rules or preference.

I see there are some other "concepts" embedded in the above statements / observations and therefore potential candidate "entities" or "tables". For example, I keep referring to component(s) / composite structure(s) for Name? Also, Name Purpose might be an entity itself (System of Record Name, Legal Use; Preferred, etc.). This may sound like theory-speak and overkill - but I have a need for this functionality.

So, just to scratch the surface of implementing this - how do I get say three Name "component" records to concatenate to represent the "Full Name" for a particular person?

Really appreciate the help.

[comment]

Let me take this in order of increasing complexity:

1. To select a single name-item for a contact, use a field in the Contacts table (that's what we had up until now).

2. If multiple items need to be concatenated to compose a single "preferred" name (for any occassion), they need to be marked in their own table, along with the order in which they should appear, e.g.

NameID  ContactID   Name      Type      Order

101     123         John      Given     1

102     123         Johnnie   Nick      

103     123         Q.        Middle    2

104     123         Bulldog   Alias

105     123         Public    Surname   3

3. The next complexity level is when you have multiple "templates" (i.e. compositions of some name items) for different occasions. Well, multiple templates need to be handled just like any other "many", so that means we need a table for Templates. And since a template has multiple components, we also need a table for these, e.g.

ComponentID    TemplateID   Type      Order

11             2            Given     1

12             2            Middle    2

13             2            Surname   3

It can be easily seen that by selecting a contact AND a template, the correct name items become related.

[dmontano]

Thanks Comment, let me see what I can piece together.