Refeshing Calculated Field

[Mark L]

Experienced FMP People will probably spot the problem in a minute

1109202737-Calculation-GlobalIssue.zip

[-Queue-]

Deselect 'do not replace existing value for field' or change the PatientID calc to

Case( not (IsEmpty(Patient_LastName) or IsEmpty(Patient_SSN)); Upper ( Left (Patient_LastName; 4) & Right (Patient_SSN; 4) ) )

If you choose, the latter, it will only work for the first name/ssn combo you enter. It will not update if you change your mind, unless you deselect 'do not replace' also.

The global one will not change unless 'do not replace' is deselected also. But I don't see what good an auto-enter global calc is anyway. I would think a regular global calc or unstored calc would work just as well.

[Ender]

I would advise against using user editable fields (name, SSN) as relational keys. These will cause you problems down the road when you get patients that don't have social security numbers, or you have patients that change their names.

[-Queue-]

And, of course, there is the possibility of having two patients with the same first four letters and last four SSN digits.

[Mark L]

Ender-

You make a good point - and I do have some experience with this - my key is actually 1st 4 of Last Name + Last 4 of SSN + Birthdate. Your point is still legitimate - even though this is intended for small offices.

If I persist in doing it this way - I'm still concerned about creating a key and retaining it even if a person changes their name or SSN. One of the business rules calls for the ID to remain regardless of changes to the other data - any idea on how to lock it down after creation?

Thanks,

Mark

Modified Comment:

PS - Queue - Thanks for highlighting this issue too. So what would you guys suggest, just an auto-generated key?

[Mark L]

-Queue-

Thanks for the suggestions - they are great and it solves my question about keep the inital value regardless of subsequent changes.

Sorry I missed that in my inital reading of your solution.

Again - much thanks!

-Mark

[bikergeek]

First off, you're not allowing for last names of less than four letters. That means you can't guarantee the IDs will be the same format [4 letters, 4 digits] because only as many letters as there are will be used, up to four.

Second, to create a static value that is locked against change, try using an auto-enter calculation and formatting the ID field as not enterable in browse mode.

Patient_ID auto-enter calc [replace] =

Evaluate ( "Let ( [ letters = Upper (Left (Patient_LastName; 4)) ;first4 = If ( Length(letters)=4 ; letters ; letters & Choose( Length(letters); " & Quote("") & " ; " & Quote("***") & " ; " & Quote("**") & " ; " & Quote("*") & ") ) ;last4 = Right (Patient_SSN; 4) ] ; If (Length(Patient_ID)<8;first4 & last4 ; Patient_ID) )"; [ Patient_LastName ; Patient_SSN ] ) )

This generates a code that, once it is generated (as measured by checking to see if it is 8 characters long) is not changed again by calculation. It uses asterisks as filler characters if the last name is less than 4 letters long.

To save trouble (in case of user error when entering data), you can create a button called "Commit" that places the values from the lastname and ssn fields into hidden fields that this calculation can then use. The commit button can launch an idiot-check script to make sure the user is satisfied with the values entered into the name and SSN fields before creating the ID.

[Ender]

Why bother with this ID system? Why not use an auto-entered serial?

[Mark L]

I had speculated about this - briefly in a previous posting.

The people I'm developing this for have requested this - but honestly, I think it's a carryover from the pre-computer era - where you needed to have an ID number - but didn't have a automated way to administer it. By applying rules to the data people had on the printed pages in front of them - it made for a systematic way of handling things.

The only other catch is there may be other systems in the office that already use this system - and this is a way to keep the paperwork in sync.

The more I think about it though - the more it would make sense to just have an automatically generated id.

-Mark

[bikergeek]

Sometimes people don't have the time/skills/etc to make over a crappy existing database. Short term fixes which make the existing product perform better get the developing user practice working with the tools of development. Perhaps when a complete redesign is unavoidable, that user will be better positioned to make the right decisions.

In the meantime, why not offer a choice of solutions, with the understanding that the better ones almost always involve more work up front for far greater savings of time and energy in the longer term?

[comment]

I happen to agree with Ender, but if we're talking about choices, then a calc field =

Upper ( Left ( LastName & "****" ; 4 ) ) & Right ( SSN ; 4 )

although not equally imposing, will nevertheless do the same as your calc (after you correct your typo).

[bikergeek]

Your use of concatenation is definitely a better idea than my choose function, but it fails to meet the standard of creating an initial value automatically and then leaving it unchanged if the dependent fields should change.

revised Patient_ID auto-enter calc [replace] =

Evaluate ( "Let ( [ first4 = Upper ( Left ( Patient_LastName & " & quote("***") & " ; 4 ) ) ; last4 = Right (Patient_SSN; 4) ] ; If ( Length(Patient_ID)<8; first4 & last4 ; Patient_ID ) )" ; [ Patient_LastName ; Patient_SSN ] )

Hmm. Typo? This was copied right from a working sample DB calc field. Shouldn't be any typos.

[comment]

If you don't want it to change, why do you include dependent fields in the Evaluate() function? What reason is there to use Evaluate() at all - other than making it utterly unreadable?

If you don't want it change - use auto-enter of the simple calc; if you do want it to change - use a calc field.

[bikergeek]

Because the auto-enter will do its thing on creation of the record, which may be before the current design gives the user an opportunity to populate the dependent fields with the needed data.

The calculation I provided does not assume other things about the design of the database, which makes it a solution more likely to provide the desired result than something that may look more straightforward to you.

[Mark L]

bikergeek-

Your calculation is most eloquent - and it seems to meet all the requirements - thanks so much.

Also thank you to all who have contributed to this post - to Queue, Ender and comment too. You guys are amazing.

I'm off to tackle my next problem - if anyone would like to take a look - it's posted in the Portal section: Portal Pre-Population Issue

A sincere thanks once again,

Mark