I thought 7 got rid of the "field piping" problem?

[spankalee]

I'm trying to set up a relationship with an invoice table where I want all invoice line items on invoices after a certain date. There are three tables in question Invoices, InvoiceLineItems, and Inventory.

Invoices has a Date field, InvoiceLineItems has a ProductCode, and Inventory has ProductCode and a global Date field.

What I want is a relationship between Inventory and InvoiceLineItems with two conditions: Inventory::ProductCode = InvoiceLineItems::ProductCode, and Inventory::LastStockCheck < Invoices::Date.

The relationship dialog has drop down menus for the tables near the top, but their both grayed out. I can't select the table Invoices from the Inventory<->InvoiceLineItems relationship. I can create a calculation field in InvoiceLineItems that equals Invoices::Date, but this can't be stored or indexed. I can also base my relationship on this calculated field.

So it looks like it will work, but I'm worried. FM6 would give a warning saying the relationship wouldn't work because it's based on an unstored calculation. I get no such warning here, but I'm still wary.

Also, since the relationship dialog has drop-down menus for the tables, is there ever a situation where you can choose the table, where they're not grayed out?

Thanks -J

Version: v7.x

Platform: Mac OS X Panther

[Kurt Knippel]

It sounds like you are trying to make a relationship 2 tables away. You will never be able to do that. However I think that you just need to setup your relationships normally. In fact you may need 2 different table occurances to accomplish waht you are looking to do.

I also do not think that you can base relationships on pipelined data.

You will need to setup the following:

Invoice -> LineItem <- Inventory - This is you standard many-to-many relationship structure.

LineItem -> InventoryAvailable (another occurance of Inventory) - This is the special relationship and you'll need the key fields to exist in those two tables.

[BruceJ]

I just tried to recreate the same three files and here's what I found.

I tried putting a LastItemChecked date in the LineItems table that calcs the date from the Inventory table. However, when you use a relationship condition other than "=", you cannot allow creation of related items in the LineItems file using the realtionship from Inventory to LineItems.

This makes some sense now that I see it, becuase withthe two conditions (ProductCode & LastCheckedDate) it would know what ProductCode to put in the LineItem, but the date wouldn't be clear.

I played with it and decided that Kurt had the best option. I've attached it as an example. I didn't do anything with the interface, but the structure, relationships and the value lists work. That second appearance of Inventory is really just to draw the value list from.

The value lists always take me multiple trys to get them right, I still don't understand them completely yet.

Version: v7.x

Platform: Windows XP

[spankalee]

BruceJ said:

I just tried to recreate the same three files and here's what I found.

I tried putting a LastItemChecked date in the LineItems table that calcs the date from the Inventory table. However, when you use a relationship condition other than "=", you cannot allow creation of related items in the LineItems file using the realtionship from Inventory to LineItems.

This makes some sense now that I see it, becuase withthe two conditions (ProductCode & LastCheckedDate) it would know what ProductCode to put in the LineItem, but the date wouldn't be clear.

Well, I'm not trying to create InvoiceLineItems from the Inventory, they're created from Invoices. I'm just trying to find out how many units of a product were bought after the last inventory count.

In FM6 I had to do this with multiple scripts and finds. and it was very messy. I was hoping to do it with one script and relationships instead of finds with FM7.

I haven't had too much time to work on this since I posted, but I should get a few hours in today. Like I said I didn't get any warning messages that my relationship based on an unstored calculation wouldn't work, but I don't trust it. So I'm going to set everything up and test it out a bit. I'll report back on how it works.

Oh, and I'm still wondering why the Relationship dialog has grayed out drop-down menus for the tables. I've been trying to figure out when you might be able to choose different tables there.

-J