Problem with External Table Writes

[Tony Martin]

Hi guys, I've been looking high, low, near and far; and apparently I'm no closer to realizing my solution, so here goes.

I have one Table for Property records, and I have one table of user records for real estate agents.

I have a layout in which Property records will be selected in find mode, and I will perform some calculation of metrics via scripts on them. Once I've calculated the metrics, I need to be able to ensure that if I'm compiling metrics for User0005 (for example) that when I go to insert this data into the Users table, that the record I'm updating is in fact the record that corresponds to User0005.

I've currently got a unique, serialized, unmodifiable primary key for both tables, and corresponding null-optioned foreign keys with respective relationships defined with the Relationships/TO view in Manage -> Databases.

The problem I'm having at the moment, is that no matter how I try to reference my User record, the scripts I'm currently running will only modify the first record in the User table. This is a big problem.

I've tried to pull all unique id's for the Users table with a call to ValueListItems(), using MiddleWords() to isolate the individual id's iteratively. This part is functioning, as I've used Show Custom Dialog [] to verify that each loop iteration contains the correct record identifier. But I'm still completely stuck on how to tell FileMaker "Update Field X in the Users table record identified by the current identifier I have stored".

Any thoughts or advice would be very appreciated, and very welcomed.

Thanks again guys :

[comment]

I don't understand your basic situation. The "table of user records for real estate agents" - is it a table of users or of agents? Or are your users agents? What is the relationship between this table and the Properties table? When you say "I'm compiling metrics for User0005 (for example)" - how do you know you are doing this for this user and not another - did someone select this user, or is it just the current user? And where will you store the result of a calculation performed on Property X for User Y, if you don't have a join table?

In short, please elaborate.

Edited June 20, 2009 by Guest

[Tony Martin]

It's a table of agents, but the agents are Users. You can substitute user for agent or vice versa in all instances, the terms are synonymous in this case.

There is a field within each property record which contains the Agent assigned to that particular property. This field will be pre-populated prior to this layout being accessed.

The reason I know I'm accessing the right user is because I'm performing Find Mode search for properties assigned to each Agent in the ValueList that contains the usernames. Essentially, since I'm compiling stats for all users I'll never be "missing" any of them.

I hadn't thought of using joined tables, but maybe that's just the functionality I need.

I think most of my problem at the moment is a seemingly complete inability to use proper vernacular, and non-ambiguous terms :

Again, I was looking for a way to tell Filemaker "I have the identifier for a record in a foreign related table stored in a local variable, and I want to populate a field in the record that corresponds to that identifier in the foreign table."

[comment]

There is a field within each property record which contains the Agent assigned to that particular property.

OK, so each property is assigned to ONE agent only (if that's not so, you do need a join table). And we know who that agent is, because it is the only related record in the Agents table.

So now the question is what are these "metrics" that you need to compile for the users?

I think most of my problem at the moment is a seemingly complete inability to use proper vernacular, and non-ambiguous terms

Just explain this the way you would to an employee that needs to perform this task using pencil and paper.

[Tony Martin]

The math calculations for the metrics seem fairly rudimentary, I shouldn't need any help there. The problem I'm currently having is that while I'm able to write into the correct table across the relationship, now I'm having the problem of Filemaker creating new entries in the destination table, when all I want to do is update the values in the pre-existing records there.

So I guess the best way to describe what I need is:

"The ability to take data from a localized script variable when the context for the layout is in one table and insert that data into the field of a pre-existing record in the destination table."

[comment]

The math calculations for the metrics seem fairly rudimentary, I shouldn't need any help there.

Nevertheless, I think it would be best if you explained this, because something doesn't fit here. Normally, a calculation should be calculated, not scripted. If it's based on data from one property, it should be calculated in the property record. If it's based on data involving all of agent's properties, it should be calculated in the agent's record, using aggregate functions over the relationship. A script that writes down the results of a calculation violates the rules of normalization.

So I guess the best way to describe what I need is:

"The ability to take data from a localized script variable when the context for the layout is in one table and insert that data into the field of a pre-existing record in the destination table."

If you are on a layout of Properties, and you set a related field in the Agents table, it will modify the first related agent record. The way you explained the relationship, there should be exactly one related agent record, so you shouldn't be having these problems. If setting a field in Agents creates a new record there, or modifies the wrong record, then something must be wrong in the way you have defined your relationship.