Filtering data using relationships

[code_geek]

OK, I thought that I understood table relationships, but I'm still trying to wrap my head around it, and I'm feeling dumber with each failed attempt.

I've uploaded a copy of my file, which should help illustrate my problem.

I have 3 tables - Customers, Stores, and Work Orders.

Each table has a self-joined instance. The ViewCustomers object for viewing a list of customers thru a Portal. The ViewStores object links to a global variable "gCustomerID" in the Customers table so a 2nd portal can show only the stores that belong to a specific customer.

That part is working fine, however...

I really make a mess of things when I try to link Work Orders to a Store by a "StoreID" field. Like I said, I'm still trying to wrap my head around not only the table relationships, but also the layout design, where the layout table is defined compared to which table the Portal is using along with the individual Portal fields.

This is how I want it to work: The user starts at the "Home" layout, selects a customer, then a store. The "Store Details" page opens up, and from there you can see Open Work Orders, and create a New Work Order. The problem is that I'm trying to set up a self-join to the Work Orders table using a global variable "gComplete" so in one tab I can show only Work Orders that are Open, and in the other tab I can show only Work Orders that are Complete.

I also don't understand how I can get the Customer Name to show on my "Store Details" layout. I want to somehow pull it from the Customers table without creating a redundant field in the Stores table. In SQL, I would have said something like SELECT Name FROM Customers WHERE CustomerID = variable

Can someone tell me how many things I'm doing wrong? I am welcome to any and all criticism, anything that will help me understand what I should be doing.

Thanks!

Stores.zip

[mr_vodka]

Create a new Table occurrence of customers ( it will be placed on the graph as Customers 2 ) and key it on customerID to ViewStores. Then place the field from the related Name field from Customers 2 on the layout and it will display.

I would suggest that you read the whitepaper linked in my signature file.

Edited April 23, 2008 by Guest

[code_geek]

[color:blue]Thanks mr_vodka, that worked...

but how do I get only the "Completed" Work Orders to display in a portal?

I've racked my brain and tried 100 different things (ok i'm exaggerating) and I just can't figure it out. I've been perusing the Whitepaper you have linked. Good stuff, I'm printing all 101 pages to read tonite - seriously. I hope to learn a few things, I've got to get this done! :cofeebrake:

[mr_vodka]

You dont need another tab to do this. You can do it this way. Tabs are a layout presentation tool, not a native filtering tool.

Stores_modify.zip

[aldipalo]

Hi John:I have a question for you. I tried setting this up based on a "Similars" table. Whereby, I can isolate client contacts (People who actually give me the work) vs all other contacts within the same company. So the tables are:

1. Company

2. Contact Names layout

3. Contact names Similar

4. Contact Names Similar type

1 is related to 2 by ClientID [=]

2 is related to 3 by ContactID [X] & ClientID [=]

(These work fine. I have no problems with them.

3 is related to 4 by ClientID [=] & (3)gType(Global [=] (4)c_ClientType (Text field autoenter calc)

When I click the radio button nothing happens. The c_Client field is properly populated with the correct 'Yes' or 'No.' Why am I not getting the same reaction as your example? Is it because they are self joined tables?

Al

[code_geek]

[color:blue]Thanks John! I appreciate the modification you did to my Stores file, you made it work exactly the way I wanted it to! You made my day, you have no idea. It makes perfect sense now that I'm able to see it done correctly. Btw, that whitepaper is an excellent read. I got halfway thru it last night, I should finish it tonite. Lots of invaluable tips in there, I learned a lot!

-shawn