How to (not) duplicate parent info in children records

[stefangs]

Hi all,

 

I have a file with investments. A portal beneath based on a normal serial number shows all children records for that investment in a table called transactions. The main file also specifies, among other things, the currency and the status (open or closed).

 

To make it a little less simple, but hopefully more useful, i want to display results of the investments over a particular period. My tracking filter is another TO of the main file where I want to be able to disinguish among records by currency and status.

 

In the main file, I have a global key that is something like

 

key (text) = currency|GetAsText (status) (one for each currency)

 

The relationship uses this field and two global date fields that let me specify the period.

 

This works in a way, but it feels incredibly clunky. Here's what I don't like about it:

 

- I am duplicating the status and currency fields in the transaction records (with a lookup). Then, I string them together to replicate the key from the main file. When the status changes, I need to run a script that looks up the contents again.

 

- To properly filter by currency, my table list is rather cluttered with duplicate relationships, the only difference being the currency (I don't want to pretend the amounts matter, but the currency doesn't, so they should be separate). Also, if I were to add even a single investment in another currency, I need to duplicate relationships and summary fields all over again.

 

Hope this makes sense. Any ideas on how to make this a bit more elegant and manageable?

 

Thanks,

Stefan

[Rick Whitelaw]

Why wouldn't you use an auto enter serial as your PK in the main table and a FK field in the child table(s)? No need to look up anything as far as I can tell. Each time you create a transaction the parent PK would automatically be inherited as the FK in the child table thereby linking a transaction to an investment. That said, I'm not familiar with your design, so . . .

[comment]

Hope this makes sense.

 

I am afraid it makes very little sense. The thing that is missing the most is an explanation of what are you actually trying to accomplish here. I am going to take a guess and make the following suggestion:

 

1. Create a new table called Viewer (you could use a TO of Investments for this, but let's make this clearer); in this table, define two global fields: gCurrency and gStatus;

 

2. In the Investments table, define two global date fields: gStartDate and gEndDate;

 

3. Define these two relationships:

 

Viewer::gCurrency = Investments::Currency

AND

Viewer::gStatus = Investments::Status

 

and:

 

Investments::InvestmentID = Transactions 2::InvestmentID

AND

Investments::gStartDate ≤ Transactions 2::Date

AND

Investments::gEndDate ≥ Transactions 2::Date

 

where Transactions 2 is, of course, another TO of Transactions. If you prefer, you could make an entirely separate TOG for this, using a separate TO of Investments, too.

 

 

4. Place a portal to Transactions 2 on a layout of Viewer, along with the four filtering global fields:  gCurrency,  gStatus, Investments::gStartDate and Investments::gEndDate. This portal will show any transaction that meets the four criteria - regardless of being a child of any particular Investment (this is the part that's most unclear in your description).

 

 

---

 

Keep in mind that you could replace (almost) all of the above with a single filtered portal - provided that the amount of transaction records to be filtered remains reasonable.

Edited March 2, 2015 by comment

[stefangs]

I am going to take a guess and make the following suggestion:

 

Thanks for the suggestion! I followed your directions and I realize where my first post didn't explain enough.

 

As you said, the portal you described will show all children from transactions. So it isn't filtering anything. By selecting a particular combination of gCurrency and gStatus, I'd like the portal to show only matching transactions records. Right now, changing the values in the globals doesn't affect the records (or I've got it wrong).

 

And this is where it gets tricky for me. The info on currency and status does not exist in the transaction records (unless I look it up and duplicate it).

 

Does it make sense now?

 

Thanks,

Stefan

[comment]

As you said, the portal you described will show all children from transactions. So it isn't filtering anything.

 

No, that's not what I said, and that's not what should be happening. The portal I described will filter the transaction records by their parent investment currency and status, and by their own date.

 

EDIT: I did, however, omit a important predicate from the second relationship. I have corrected my post now.

 

What I did say is that it will treat all transaction records alike. It was important to say this, because originally you started from a TO of Investments - thus suggesting that only transactions children of the current investment would be included.

Edited March 2, 2015 by comment

[stefangs]

Ah yes, it works now. I added a couple of relationships that let me display the actual name of the investment in the portal, so it's easier to read. With my concatenated keys, I would have never accomplished this.

 

Thank you so much!

 

Stefan

[comment]

I added a couple of relationships that let me display the actual name of the investment in the portal, so it's easier to read.

 

I believe an unstored calculation field in the Transactions table = Investments::Name would be cheaper to implement.

[stefangs]

Now that's actually a very interesting point!

 

By 'cheaper' do you mean in terms of direct approach/easy to understand or by FM design? I'm curious because I thought that avoiding fields whose contents can be derived from what you already have would be something a FM student should strive for.

 

Adding an extra field certainly makes the TO graph easier to understand. Reducing the number of fields makes the field list more focused. A philosophical question?

[comment]

 I thought that avoiding fields whose contents can be derived from what you already have would be something a FM student should strive for.

 

That is largely correct (not entirely, because the way you put it would make any calculation field illegitimate!). Here, the problem is how to display the contents of a field that isn't available through the existing relationship chain. If you would have tried to lookup the name into a field in Transactions, thus storing the same information in two places - that would indeed be breaking normalization rules. OTOH, an unstored calculation field is just a pointer to the place where the real data is stored.

[stefangs]

Makes sense. I think I'll go for the unstored calc then. With my lingering FMP v.<7 frame of mind, it's much easier to understand than dragging all these lines around in the TO graph (although that is slowly becoming less intimidating as I progress).

 

Thanks,
Stefan