Dynamic Field Formatting/Style based on Validation

[keckfmp]

We plan to have a users registration database driven by Lasso. We will always allow data from Lasso into all fields. However, in an effort to acheive data consistency, we will manually authorize these user accounts. Certain fields should only contain data that is validated by a value list. In the event that a particular field doesn't validate we'd like to clearly identify it by changing to field style for that record to indicate so (perhaps bold and red). I don't think record-level formatting is possible, but perhaps someone has come up with a clever workaround.

We cannot provide the value lists on the Lasso side due to confidentiality issues. Any thoughts?

[Steve T.]

We cannot provide the value lists on the Lasso side due to confidentiality issues. Any thoughts?

So, you do not want to validate through the user-shown options on the web... but can you have FileMaker validate the field and notify the web user that the value is invalid or do you need to just accept everything and not even tell them the data is not valid?

Assuming the latter, a simple/easy way to draw attention to it is to use calc fields to label the data in big red letters "NOT VALID" next to the field in question on the layout if it's not what you want...

validator = if(thisfield = "Freshman", "NOT VALID", "")

If the field is valid, no value for the validator calc field is shown (it's blank and empty and does not show on the layout). If you want it obvious as you browse records, you can make it REALLY BIG and put it in the top right corner in a box or something.

--ST

[keckfmp]

Thank you Steve. Yes, the latter is true. We will accept all data and validate/error correct internally. I have come up with a similar approach using container fields that displays a green checkmark if the field validated correctly, or a red X if it doesn't. I still have a problem though:

We will accept data into a particular field. I'm trying to set up the validation by determining if that data is a member of an existing value list. The value list is defined by the contents of that field itself. In effect, the value list only contains data that currently exist in the database and thus the Lasso-entered data only validates if it's in the value list. The problem is that FMP allows Lasso entries of non-existentent data and then simply updates the value list so it always validates. So I'm kind of in a circular situation.

What I need to do is somehow validate the data by determining if it already exists in the database. If it does not, it should be tagged as invalid and only tagged valid once updated by a staff member. I'm looking into the ValueListItems design function, and then validate against that versus the value list itself, but I'm not getting very far. I don't know how to validate data in the field by determining whether it exists in the list within the ValueListItems field. I then need a way to update the ValueListItems field. Any thoughts on ways to pull this off?

[keckfmp]

I'm getting closer. Here's what I have:

Three fields - Institution, Institution_Value_List_Items, and Institution_Valid. I also have a value list called Institution which uses values from the Institution field. The field are defined as follws:

Institution (Text, Validated by Institution Value List)

Institution_Value_List_Items (Calculation, Result is Text, Calculation is stored): ValueListItems( Status( CurrentFileName) ,"Institution")

Institution_Valid (Calculation, Result is Text, Calculation is stored): If( PatternCount( Institution_Value_List_Items, Institution ) > 0, "Valid" , "Invalid" )

This works properly, but as the Institution_Value_List_Items field is stored (it has to be or I'll be in the same circular situation where everything is valid), the result is that this field doesn't get updated as records are added or deleted. Therefore I need a way to "refresh" the Institution_Value_List_Items field so the data includes the newly added items. Thoughts?

[Steve T.]

Well, nothing comes to mind off the top of my head, but what I did in something kinda similar was use a "pending" database which held yet-to-be-approved records. When approved, the appropriate records are imported into the final db via script. This would not work in all situations, however, since sometimes the pending info NEEDS to be in the final db.

Hmm... I wonder if you could have a manual valuelist (APPROVEDlist) and compare it to your LIVElist.

validator = (if status = "pending" and APPROVEDlist != (does not equal) LIVElist, "NOT VALID", "")

Maybe you could have a script add the value to the APPROVEDlist if staff say it's ok? My syntax may be off but that's my untested idea, though. I've never tried comparing valuelists, though, so don't know if it's possible.

--ST

[keckfmp]

It just occurred to me that I could unstore the Institution_Value_List_Items field and create a global to capture the current state of the Institution_Value_List_Items field via a script. This global would then be used in the Institution_Valid calculation. Anyone see any flaws in this approach?

[Steve T.]

Anyone see any flaws in this approach?

Hmm... try it, but just be careful of the script-over-the-web issue and the globals-over-the-web issue.

--ST