Calculated Fields and Imported Data

[Shrubman]

Hello all,

I'm well underway adapting iSolutions' ClientTracker to my needs. I am having a problem, though, with calculated fields not populating on import. For instance, I just brought in my projects table data but as I look at the results in form view, the contact information for the project that is supposed to populate from a calculated field is blank. The calculation seems valid because I new entries work just fine. Is there a way to force a calculation manually after import. I don't look forward to manually linking each of the hundreds of past projects.

Thanks,

Evan

PS: Yes, I did tell it to perform lookups and calculations on import.

[Fenton]

Have you updated to 7.0v3? Because earlier versions (especially pre-7.0v2) had a real problem with this.

[Shrubman]

Yes, I'm using FMPro 7.0v3

[Fenton]

Did you import the ID fields? Because it looks like to me that they've made a kind of fancy mechanism so that the drop-down value lists show only the names, instead of the IDs for choices. That's OK, but the real data is the ID fields. Look at the table view of Projects and you'll see all the fields.

In short, unless you imported the ID fields themselves you don't have the data to match. If you did import them, the data would show.

[Shrubman]

I think I may have found a partial answer to my problem. The FilemakerPro 7 Bible says you cannot import data into calculated fields. If that is the case, how do I initiate the calculation after the relevant other fields are imported. Like I said, the calculation script itself seems to be valid since new entries populate accordingly. The problem is with entries I imported.

[comment]

I get the feeling you're mixing terms here. Calculation fields update automatically when a referenced field is changed. I suspect you are refering to regular fields, with auto-entered calculated values. If you want these values to be auto-entered in imported records, you need to select "Perform auto-enter options while importing" in the import dialog. Otherwise, auto-entered values will not be entered.

[Shrubman]

While it is true that I have some confusion over the difference between a calculated field and a text with with calculated auto-entry, the fields in question are of both types. Neither one seems to fill, regardless of the import dialog setting (which is selected).

[Fenton]

I don't think it's so much a calculation issue with the Import. It's that you must import the IDs if you expect them to have data. They are NOT meant to populate by importing some other field (even though it might appear that way because they are auto-enter). They must be imported directly. They are only auto-enter by calculation as a tricky way to avoid showing the IDs in the value lists. It's a good trick I suppose; but it would have helped if there was at least a comment to the effect. But, in any case, one would expect to import the IDs, since they are the relational keys.

So, if you have the data somewhere, Import again, and import the IDs.

[Shrubman]

Are you saying I need to import the IDs into every table that calls upon them? I have imported the IDs into the client table.

[Fenton]

You said, "I just brought in my projects table data..." From where, some other solution? If so then must have foreign keys for the client or contact in the source table; otherwise how are your tables related?

I'm just saying that the iSolutions solution keys are just regular fields, which need data imported into them like any other fields. The clever (but undocumented) trick they've used, to make the value lists look better, has led you to the illusion that those fields are somehow like lookups, and will populate themselves from somewhere else. There is no way to even use Relookup or Replace to trigger some kind of data transfer. You must import data into them.

[Shrubman]

I imported the projects table data from another basic filemaker db I had started. My client and contact information was brought in the same way. Both the client table and the contact table in the new FM database in question have the client id field filled with data. The client ID field is the primary key for the client table and a foreign key in the other two tables. Again, I have left the field definitions pretty well alone as they came from ClientTracker.