Post Operation

[jas]

Is a post operation limited to 3 linking fields only? If so, is there a way to get around this? I have 2 tables, one with 30,000 records, the other with 70,000 records. I need to post a key field from the first table to the second and the only way to be certain the post is correct is by using 5 linking fields. However this does not seem to work. I tried writing an xbasic script that steps thru the first table, then, at each step fetches thru ALL of the records in the 2nd table checking that the 5 fields from the 1st match the 5 fields from the 2nd. However, at the rate the script was running, it would probably take several days to finish.
I sure could use some help here.

Jim Smith

[Stan Mathews]

Is a post operation limited to 3 linking fields only? If so, is there a way to get around this? I have 2 tables, one with 30,000 records, the other with 70,000 records. I need to post a key field from the first table to the second and the only way to be certain the post is correct is by using 5 linking fields. However this does not seem to work. I tried writing an xbasic script that steps thru the first table, then, at each step fetches thru ALL of the records in the 2nd table checking that the 5 fields from the 1st match the 5 fields from the 2nd. However, at the rate the script was running, it would probably take several days to finish.
I sure could use some help here.

Jim Smith

I've never encountered a situation where five linking fields were required. Have you considered adding a field to each table and populating it with the concatenation of the linking field values, then posting based on that field?

[jas]

Post Operation

Thanks Stan
Your suggestion seemed like the perfect solution. So, this is what I did:

I created a calculated field (Postlink) in each table that was a concatenation of the appropriate fields. Then I created a Post Operation using these fields as links. Didn't work. So I then created another field (Plink) in each table that was not a calculated field. Then I created update operations for each table and put the value that was in the calculated field (Postlink) into the regular field (Plink). Then I deleted the Postlink field from each table, packed the tables, defined Plink as an index in each table, then created a new Post Operation. Didn't work!
I am totally baffled!

Jim Smith

[Tom Cone Jr]

Jim,

I don't know why you're having trouble. Haven't used multi-field links for this sort of thing personally, either.

I wonder if you could define a 1:1 set using the Plink field (in each table) as the link? If so, you might be able to define a simple update operation based on the set, to assign the key field value from one table to the other. One advantage to this approach is that using the set you could verify that the linkage is working as you expect.

In addition, if you didn't want to use the update operation, the script to step through all the records and assign the key value from one table to the other would be pretty simple, and should be quite fast.

Recommend you make a couple of good backups before attempting this. Even better, experiment with temporary tables you could discard if it blows up on you.

Good luck.

-- tom

[Stan Mathews]

Thanks Stan
Your suggestion seemed like the perfect solution. So, this is what I did:

I created a calculated field (Postlink) in each table that was a concatenation of the appropriate fields. Then I created a Post Operation using these fields as links. Didn't work. So I then created another field (Plink) in each table that was not a calculated field. Then I created update operations for each table and put the value that was in the calculated field (Postlink) into the regular field (Plink). Then I deleted the Postlink field from each table, packed the tables, defined Plink as an index in each table, then created a new Post Operation. Didn't work!
I am totally baffled!

Jim Smith

We are often dismayed by the use of "Didn't work." as a description of a result. Did you get an error or were no records posted when the operation was run? Did you examine the concatenations in both tables to see it the linking fields do indeed hold the same strings? Do the fields used in the concatenation have the same type, width, and decimals in both tables?

The more you give the more you get, and sooner.

[jas]

Post Operation

Stan, Tom:

I'd like to thank you both for your input. You're right. Stan, "didn't work' is not very specific. Actually, the Post Operation would run, but '0' records would be posted. My solution to the problem turned out to be very simple. I dropped the idea of using a Post Operation, and instead, used the concatenated fields (created per Stan's excellent suggestion) in each table as the filter for the Query I need to run. The Post operation was to create a unique field that would identify related records in each table. Turns out the concatenated field does exactly that!

Again, thanks for your help

Jim Smith

[Stan Mathews]

Stan, Tom:

I'd like to thank you both for your input. You're right. Stan, "didn't work' is not very specific. Actually, the Post Operation would run, but '0' records would be posted. My solution to the problem turned out to be very simple. I dropped the idea of using a Post Operation, and instead, used the concatenated fields (created per Stan's excellent suggestion) in each table as the filter for the Query I need to run. The Post operation was to create a unique field that would identify related records in each table. Turns out the concatenated field does exactly that!

Again, thanks for your help

Jim Smith

Glad you found a solution/resolution.

For future reference, if you find yourself in a situation where

Actually, the Post Operation would run, but '0' records would be posted.

then the linking values didn't link any records. This can be due to defined field width, decimals, etc. One way to diagnose this is to define the master table linking expression and use the navigation buttons to view some linking values and then do the same for the transaction table. If the structure of the values don't appear the same you need to redefine one or both of the expressions.