AnsweredAssumed Answered

Can a Single Field be defined upon two relationships and one operation?

Question asked by thomasfilemaker on May 30, 2010
Latest reply on Jun 4, 2010 by thomasfilemaker


Can a Single Field be defined upon two relationships and one operation?


I am trying to define a single field that uses only one operation that is based upon two relationships.  Presently I am able to achieve a result with two operations; one being a lookup field defined by a relationship to another database, and the second operation being an external script from a third party plugin. Can this be done without having to rely on the third party external plugin?



I thought I could define a lookup field populated with simple task's ' that a user would pick in a FM database, for example, called Work Files.  This choice in field 'a' would populate field 'b' that is a corresponding code 'b' [for example, a numeric code] that is always the same for each simple task 'a'.  'B' is set by a looked-up value from a related table in another FM Database [called Tasks]  that has in its records, fields a: tasks,  b: unique code for each task, and r1: payment for task 'a' and r2: payment for task 'a', etc... r3, r4, r5... Whereby each 'r' value being unique for each zip code 'z'.  The description of each task 'a' and the code for each task 'b' do not change.  The payment 'r', for each task 'a' changes as is dependent on which zip code, 'z' the task 'a' is done.


I thought there was a way you could define a single field that would result in a defined 'r*' payment that is defined in every new record in Work Files, that allows the user to chose a task in a look-up field A in Work Files.  Field B, is populated as a result of a simple lookup field that is defined by a simple relationship, where A is matched in the look-up field A in Work Files and the field A in Tasks, the other Database in this example.  B is populated in Work Files by the simple relationship: when A:Work Files equals A:Tasks, then B: Tasks in the result in B:Work Files.


For each B (job code), there is a unique R (payment) and for record in Tasks, there are many R's (payments) that can be defined and each one is unique for each zip code 'z' in Tasks.  Is there not a solution, i.e. one field definition 'R' (Payment)  in Work Files, that can be defined by a relationship of both a unique value 'a' and a unique zip code 'z'  ---  whereby the zipcode is chosen for each new record in Work Files, and the user then chooses a task which is unique for each record, and is chosen based upon the choice of the user, and the resulted payment is populated in a field in Work Files, that is based upon the relationships described above.  I am able to write a simple relationship that defines a field that populates the payment based upon a single zip code that never changes, and when the user chooses the task, the resulted payment is defined for the zip code that is the default zip code.  This is easy, and when the user changes the zip code in a record, I have used a external script written by a third party, that recalculates, the payment based on the changed zip code. So, in the end, I have accomplished this with two different operations, one being the relational look-up field and the second being a third party script, written to change the payment value, when the user changes the zip code away from the default zip code. 


Can this result [unique payment in each record in Work Files] be written or defined by one operation, that is one defined field, so that when a user picks a zip code and task in Work Files, a unique payment is defined in the same file that is defined by the unique relationship between each task, zip code and payment?


As for which program version of FM, I am using FM 6, 8, 9, and 11.  I have just begun to use FM 11for the last week, and am trying to upgrade some older programs, and would like to do so and not be dependent on the third party plugins which are not at present compatible with FM 11. Operating systems: XP, Mac OS 10.6, Windows 7.