Calculated Auto Enter Challenge

[SurferNate]

Hi Folks,

I've got what should bee an old and resolved problem, except I am having a heck of a time solving it.

I have two tables:

Jobs:: and Contacts::

I am trying to set up an intuitive contact entry in the Jobs:: table where I have three fields, Jobs::cName Jobs::cBusiness and Jobs::cID

All three of these fields have a match field in each record in Contacts::

Here's the hitch, I am trying to use the new FM8 autofill function to have a layout where a user can set the various contacts by filling either the Jobs::cBusiness field or the Jobs::cName field, if either one changes then the other should change by a calculated autofill. I MUST at least store the Jobs::cID field (hidden) because of a legacy data situation. In my legacy data I may have only one of the three items available and I need the other two to autofill for me. The cID is an attribute of each contact person, even if that person jumps to another company, which occurs often, all of the Jobs:: records will still remain related to that person, hence the absolute need for the cID field to be consistent no matter what. AND of course, I need the cBusiness field to relate jobs to a business no matter where their employees have jumped ship to this week.

I can imagine there may be a couple of convoluted solutions but since I have a dozen or more contacts related to a job, I need a solution that minimizes redundancy. I already have a few dozen fields just in retaining the dynamic job/person/business relationship.

Does this make sense to anyone?

cBusiness cID

cName cID

cID cName

cID cBusiness

to complicate the issue, I am trying to set it so that I have a value list of names that shows all contacts when Jobs::cBusiness is empty and only related names when cBusiness is filled.

Its a messy issue but I am focused heavily on intuitive input/output and this is a critical layout. Due to human nature, people will invariably enter bad info and I need all three fields to respond naturally when someone makes a correction. I don't mind redirecting to the Contact:: record if corrections need to be made to the actual Contact:: source data. It's the Jobs:: record that is driving me nuts.

somebody help me!...please?

:-)

Nate

Autofill_Test.zip

Edited September 25, 2005 by Guest
EDIT: Attached is a non-functional ultra-stripped demo file for your entertainment and head scratching fun. Maybe someone can finesse a desireable result and attach their solution.

[-Queue-]

I assume you are referring to the 'do not replace existing value' deselection feature offered in versions 7 and 8 and not 8's auto-fill (type- ahead) feature.

If that is the case, then make each of your fields auto-enters with the following calculations:

cBusiness:

Case(

Get(ActiveFieldName) = "cName" and not IsEmpty(cName); If( Count(Jobs.cName:Contacts.Name::ID) = 1; Jobs.cName:Contacts.Name::Business );

Get(ActiveFieldName) = "cBusiness"; cBusiness;

not IsEmpty(cID); Jobs.cID:Contacts.ID::Business

)

cName:

Case(

Get(ActiveFieldName) = "cBusiness" and not IsEmpty(cBusiness); If( Count(Jobs.cBusiness:Contacts.Business::ID) = 1; Jobs.cBusiness:Contacts.Business::Name );

Get(ActiveFieldName) = "cName"; cName;

not IsEmpty(cID); Jobs.cID:Contacts.ID::Name

)

cID:

Case(

Get(ActiveFieldName) = "cBusiness" and not IsEmpty(cBusiness); Jobs.cBusiness:Contacts.Business::ID;

Get(ActiveFieldName) = "cName" and not IsEmpty(cName); Jobs.cName:Contacts.Name::ID; cID

)

Regarding multiple value lists for the same field, you can create a calculation field in Jobs of

If( not IsEmpty(cBusiness); cBusiness; "all" )

and a calculation field in Contacts of

Business & ¶ & "all"

Then create a new relationship from the first calc to the second (I would use new TOs for each table) and create your value list based on the related Name field, starting from the relevant Jobs TO.

I included the Count( ) test in the cBusiness and cName field definitions to account for the possibility of multiple names for a single businesses, and vice versa, which is why no name/business is auto-entered if there are multiple businesses or names related to the current selection.

The not IsEmpty( ) tests ensure that two fields are not cleared automatically when one of the two modifiable fields is cleared, e.g., clearing 'Don' from the cName field in a record with Basham and 119 does not auto-clear the latter two values and leave you with a blank record.

[SurferNate]

Queue,

Thanks so much for the lesson. I'll study and apply. You've hit on my major struggle which was having fields either go blank or not update depending on what calc I used. It never occured to me to use the IsEmpty statement. You understood me even though I explained poorly. I am bringing a project I have in the works up to version 8. I should have said Auto Enter not Autofill.

:-)

Nate

[SurferNate]

Queue,

I've tried using your syntax for the calculations but I still get undesireable results. At least now I've got my head around the problem a little better though. I realize that I am using the ContactID as a primary key for another relationship and that presents a problem. I need the ContactID to update based upon both the Name and the Business. Naturally because there will be duplicates of any given name (multiple people at different businesses) and duplicates of any given business (multiple people at the same business). But hopefully not two people with the same exact name at the same exact business. I realize that a major factor in this is the order in which FM evaluates circular references. In my case, it evaluates ID first then Business then Name. I am still sorting out the proper calc sequence and statements but I'll post my solution when I get it right.

[-Queue-]

I don't think creation (evaluation) order should be an issue in this case because the calculations are testing the most recently modified field's name and value and not merely pulling data from them indiscriminately. Also, they are only returning either the current field's value or a related value, not data from one of the other fields within the table. So, unless I am missing something more complex, which is not contained within your stripped-down sample, I would consider this a moot point.

I do see where cID could be an issue, since selecting a business will return the ID of the first related business, which may not necessarily be the ID of the desired contact for that business. It may help to create both a Businesses table and a Contacts table, each with their own ID. Then you can maintain consistency and accuracy, regardless of whether one or the other, or both, has been selected, by using a bID and a cID.