Need a little relationship help between two tables

[Rich S]

Howdy, all:

 

I just can't figure out this morning why this is such a poser for me; maybe it's because instead of eating Raisin Bran I ate Raisin Brain. Please refer to the attached (which I created in .fp7 so others can see the answer though I use FMPA v13.x for everything now...including where this technique will be implemented).

 

What I want to do is sort all Table 2's data by ascending date in Table1's portal; if a company has more than one date entered for its record in Table 2 then it will obviously need to appear more than once in the portal...which is what I want.

 

Now, the tricky part: The way the solution this technique will be implemented in has two pseudo-related tables--Table 1 has only one record in it to keep it alive, but it isn't used to store data nor will it be used to insert records into Table 2: it's just for viewing the records in Table 2. (That's the way it's set up and the customer doesn't want to change it.) So, the relationship can't be just a self-join of Table 2.

 

Your help and/or guidance will be greatly appreciated!

 

Thanks,

Rich

Test.fp7 2.zip

[Matthew F]

So, what exactly is the issue?  Your portal is currently setup to sort by date and this works fine.  The only issue that I see is that your matchfield is Table2ID not Table1ID so you only have one record showing up in the portal.  (Its hard to sort only one record). 

[Rich S]

I'm sorry--I wasn't clear.

 

There needs to be a place holder field needed (and join) so the portal produces a list like this:

 

02/01/2014        delta
02/01/2014        foxtrot
02/02/2014        alpha
02/03/2014        echo
02/04/2014        echo
02/05/2014        echo
02/06/2014        alpha
02/06/2014        bravo
02/07/2014        charlie
02/13/2014        alpha
02/24/2014        foxtrot

[LaRetta]

You have two kp's joined together.  In Table1, you have it called Table2ID.  Change it to kpTable1ID and change the join to join it to Table1ID in Table2.  Neither do you have any parent IDs in the child table (Table2).  Adding 1 into those child records will produce the results you want.

 

So once that is straight, sort the portal by date and you should be set.

 

I notice you have Date1, Date2, Date3 ... those fields should (probably) be records in a related table.  If you explained a bit more instead generic Table1 and Table2 and Date1, Date2 then it would be easier for us to assist you.  :-)

 

If I misunderstand your need, please provide a bit more information.

"place holder field needed (and join)"

 

So you know you need a join table.  Please tell us more what these tables & dates represent first.  :-)

[LaRetta]

here like this:

 

 

Then change the portal to be based upon the join table and be sure to change the fields within the portal to the join as well.  You can check 'allow creation' on the join table from both sides.

[comment]

There needs to be a place holder field needed (and join) so the portal produces a list like this:

 

02/01/2014        delta

02/01/2014        foxtrot

02/02/2014        alpha

02/03/2014        echo

02/04/2014        echo

02/05/2014        echo

02/06/2014        alpha

02/06/2014        bravo

02/07/2014        charlie

02/13/2014        alpha

02/24/2014        foxtrot

 

A portal shows one row for each related record. There is no way you can have a portal show 11 rows of data, when the related table has only 6 records.

 

In addition, as LaRetta already mentioned, you have numbered date fields - all of which points to the need for another table where each date (or rather event) would be a separate record, (probably) linked to the relevant Company by CompanyID.

 

 

 

The way the solution this technique will be implemented in has two pseudo-related tables--Table 1 has only one record in it to keep it alive, but it isn't used to store data nor will it be used to insert records into Table 2: it's just for viewing the records in Table 2. (That's the way it's set up and the customer doesn't want to change it.) So, the relationship can't be just a self-join of Table 2.

 

I don't know that it's any business of the customer to say how this should be implemented. In any case, to view all records of the table from a TO of any table, you should use the x relational operator when defining the relationship.

Adding 1 into those child records will produce the results you want.

 

Now, that is soooo version 6...

[LaRetta]

No, Michael, the 1 is the CompanyID - the foreign key was empty.  LOL.

Simply, I had responded while driving.  Well I was stopped at a light.  And I did not read this thread properly.  I responded too quickly and did not even see the part about why the two tables existed.  My bad.

[comment]

Beg pardon?

[LaRetta]

In the file, I said to put a 1 in all the child records because it could not relate to Companies because the companyID was empty.  The parent record was companyID 1.  So I said to put a 1 in all the child records and it would show them all. That isn't 6 stuff ... that's normal relationship answer.  :-)

Please take back that I did '6' stuff.  I didn't.  I explained that no records showed because they weren't even related.  I hate it saying I'm so '6' ... I don't use flags to relate.  I use parent keys which was a 1.  :-)

 

I hadn't read that it was a cartesian need.  I missed that paragraph.  I was giving them a match to Parent ID 1.

[comment]

I said to put a 1 in all the child records because it could not relate to Companies because the companyID was empty.

 

I am sorry, but I don't follow your logic.  Which table do you think is the Companies table? The one with 6 distinct companies, each having a distinct value in a CompanyName__lxt field (plus a unique auto-entered serial _kpln_Table2ID field), or the one of which OP said that it "isn't used to store data nor will it be used to insert records into Table 2: it's just for viewing the records in Table 2"?

[LaRetta]

The two auto-enter serials were joined together originally.  That does not work of course so I changed Table 1's ID to say fkTable1ID and changed the join to Table2::Table2ID using =.

 

The portal was on (unnamed) table 1 so I went to table 2 and saw the children.  NONE had a table1ID in them.  So I looked and the only parent was ParentID1.  Then looking at the list of dates, it seemed he was showing he wanted all (or most) of those dates (again, I missed a paragraph that he needed cartesian).  So I said to put a 1 into the table1 foreign key in Table1 to complete the relationship.

 

When I realised he needed join, I provided the diagram, creating a join and moving the dates to it. 

 

But this is why I dislike trying to assist on something when going by Table 1 and Table 2 and why I should have read more thoroughly.  What can I say?  But I do NOT do 6-like stuff.  I made a mistake in reading it.  That's all.  May I just creep away now in proper shame?

 

But I don't do 6-like stuff.   I just did stupid-like stuff here only.  :-)

IOW, I didn't read anything - I just saw 'relationship broken' and I took the file and fixed it.  WRONG.

 

Well, I read ... but I sped-read like at stop lights and such.

[comment]

Ok, I take it back. You don't do v.6 stuff. You just need a new pair of glasses and a cop to supervise your driving habits.

[LaRetta]

 

 

It was a new habit since I got my baby rMBP and only when stopped so in heavy traffic I can use it a lot and it was a new habit so it will be easy to break and this thread had already convinced me to do so.  Glasses.  Me?    

 

Thank you for taking it back!!

 

BTW, I don't talk on phone and drive though.  That's dangerous!

[Rich S]

Thank you all for your help. I think a little customer (more correctly, coworker) education is necessary here--changes in functions and features sometimes comes at a price; pick the one(s) you really want and go with that. I just may show him John Ahn's VirtualSort.fmp12 solution to see if I/we can make something work for him--I hate telling someone "No" when instead I could say, "Let's try this..."

[Matthew F]

I'm still confused.

[LaRetta]

So am I but then I started out on this thread that way ... I'm not responding, LOL, I don't mess with sleeping dogs.