Maintain Record Continuity Between Tables

[CatLady03]

I have a database that uses multiple tables to display data. The key field in all tables is one called Symbol (as in ticker symbol). Each display panel contains fields for one specific symbol.

What I want to do is maintain continuity from one table to another when looking at the data for a specified symbol. In other words, if I'm looking at a record on one table and I click on a panel that displays data from a different table, I want it to display data for the same record (symbol) I was looking at in the first table and display the related data from the second table.

I hope this is clear... :qwery:

[coconutt2000]

I think what you need is a relationship between the two tables with a key in the first table that indicates what you're looking at, and a matching key in the second table.

If data in the first table involves several different possible data sets in a single record, a calculated key generated based on the data you're looking at, which would be related to data in the second table, which would filter the contents of the portal pointing to the related table. Otherwise, any key, regardless of how it is set, would be sufficient for pulling the right data out of the second table, so long as that key is common to the relevant records in the second table.

It has been a long while since I used FM6.

[CatLady03]

Thank you so much for responding. I do have relationships between the tables with Symbol as the key in each. When you mentioned portals, I thought maybe that might have been my problem, but it's not. I rearranged my panels to include portals to the tables containing the data, but that didn't resolve my problem. I still can't maintain continuity of the Symbol from display panel to display panel when the source is a different table.

[color:red]If data in the first table involves several different possible data sets in a single record, a calculated key generated based on the data you're looking at, which would be related to data in the second table, which would filter the contents of the portal pointing to the related table. Otherwise, any key, regardless of how it is set, would be sufficient for pulling the right data out of the second table, so long as that key is common to the relevant records in the second table.

I don't really know what you mean by "calculated key." That's a new one for me. Perhaps you can elaborate for me...thanks!

[CatLady03]

OK, I figured it out. I had a sort in the scripts that kept resetting it back to the first record. I took them away and solved my problem.

:thankyou:

[coconutt2000]

In my own lexicon, a calculated key is nothing more than a calculation field that processes the information in a record and produces a result that is used in a relationship.

One use would be for data validation. A calculated key would wait until it had valid data before outputting a result, essentially leaving the key field blank or filled with a default value. One instance where this might come in handy is when designing a database invoice system. An invoice line item's state could be validated by a calculation field. If everything is in order, the line item's calculated key output is proper. If not, the key changes and shunts the line item to the review bin.

If kosher the calc_key will equal the invoice key. If not kosher, the calc_key for a line item will equal a calculated result like, "inv_##_tmp", where ## is the invoice number. A separate relationship in the invoice table would relate to the *tmp line items, allowing for the line items to be reviewed and fixed. An override can be built into the calculation to lock the result in its kosher state, preventing the line item from shifting state at some date in the future after it has been posted, paid, and retired.