Portals from same table in layout

[nelsonkh]

I am trying to create a layout for my database that will have 2 portals from the same table. I created 2 table occurences of the same table, but am having trouble getting the information I want to show in my second one.

There are 2 original tables: Customers Log and Production Goals.

Customers Log has these fields:

Date Actual Start

Date Completion

Week Completion

Job Sold (all fields = SOLD)

Production Goals has:

Week Installed

Year Installed

Weekly Goal

Job Sold (all fields = SOLD)

They are linked by Week Completion = Week Installed, so I can run a report to see what our weekly vs. actual was.

I have a portal in the Production Goals layout that shows all of the jobs installed for that week. This is linked using the Week = Week.

I want to create a portal that shows all of the jobs that are sold but uninstalled. So I created a new table occurence of Customers and linked Sold = Sold, but cannot figure out how to get the portal to only show uninstalled jobs. I tried creating a blank date field and linking BLANK DATE = Date Actual Start (so that jobs that have not started would be linked). Can someone help?

Thanks.

[IdealData]

There are other relationship match criteria - you're probably only familiar with "="

Look at "<", ">", "≤", "≥", "≠" (not equal) and "x" (all)

These are particularly useful when using dates as your match fields.

[comment]

How can one tell if a job is or isn't installed? Your Customer Log table doesn't seem to have a field to indicate this.

[paf59]

I am having the same problem. I have a single table in the layout with 3 portals of the same table hidden via a Tab Control with nothing more than a single join condition in each. The first occurrence (Table and Table2) is an all (x) join on a single field. It works properly. The second and third joins (Table to Table3 and Table to Table4) each have a single join field with an equals (=) operator. Neither of them return the expected results. I have tried relationship sorting as well as complex joins to see if I could get a result set that I was needing. Still no luck. The equivalent SQL code is as follows:

[color:orange]select * from [Messages] m where m.Staff_ID = 5 order by m.Date ASC

So each time the Message record in the layout changes, the Staff_ID changes and the portal table should show those messages for that Staff_ID number.

I am definitely open to solving this problem. Let me know if you get a solution.

[comment]

So how exactly is the relationship defined?

[paf59]

Table_________Table2

Staff_ID x Staff_ID

Table____________Table3

Message_Date = Message_Date

Table_____________Table4

Message_From = Message_From

I have tried varied sort orders in the relationship with no sort order at the portal.

[comment]

Table_____________Table4

Message_From = Message_From

To take this one as an example: your portal should be showing all records whose Message_From is the same as the one of the CURRENTLY VIEWED record in Table (assuming your layout is defined to show records from the "Table" TO, and the portal is showing records from the "Table4" TO).

Sort order aside, are you seeing this result or something else?

[paf59]

Correct - That is what should be showing, but I am getting a single record that may show up one or more times in the portal, with no rhyme or reason as to its count for each record change in the layout. Additionally, the record (and its occurances) that shows up in the portal is not related to the record in the layout, but it does happen to be the last record in the table! So I would guess that the relationship is somewhere failing and pointing to the last record in the table.

[comment]

My guess would be that the fields in the portal are not sourced from the same TO as the portal (you probably duplicated the portal, changed its source TO and left the original fields alone).

If that's not it, you'd better post a sample file showing the problem.

[paf59]

I have made a sample database just containing the tables to test. I believe that you may be correct as I copied the portals and went in and changed their settings, so I will try that ASAP and let you know.