Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #2969 > unrolled thread

Can't Use a Portal Across a Join Table

Back to article view | Back to comp.databases.filemaker

Contents

  Can't Use a Portal Across a Join Table csampson@inetworld.net (Charles H. Sampson) - 2018-01-21 13:55 -0800
    Re: Can't Use a Portal Across a Join Table Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-01-22 18:53 +1300
    Re: Can't Use a Portal Across a Join Table Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-01-22 18:57 +1300
    Re: Can't Use a Portal Across a Join Table ursus.kirk@gmail.com - 2018-01-22 10:13 +0100
      Re: Can't Use a Portal Across a Join Table Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-01-25 09:26 +1300
    Re: Can't Use a Portal Across a Join Table csampson@inetworld.net (Charles H. Sampson) - 2018-02-03 11:36 -0800
      Re: Can't Use a Portal Across a Join Table Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-02-04 10:02 +1300
        Re: Can't Use a Portal Across a Join Table csampson@inetworld.net (Charles H. Sampson) - 2018-02-06 02:12 -0800
          Re: Can't Use a Portal Across a Join Table Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-02-07 09:15 +1300

#2969 — Can't Use a Portal Across a Join Table

I'm back to trying to understand the FMP way of implementing a
many-to-many relationship. To do this, I'm using the example in the
online FileMaker help. Three tables: Students, Classes, and Enrollments.
Enrollments is the join table between Students and Classes.

As suggested, I have created a portal from the Students table to the
Classes table, presumably through the Enrollments table. I have checked
the box to allow creation of an Enrollments table by means of the
relationship from Students to Enrollments. (I want to be able to create
an enrollment by using the portal.) I have not checked the box to allow
creation of a classes record by means of the relationship from
Enrollments to Classes. (Students are not allow to sign up for
non-existant classes, thereby creating the class.)

Now I'm at the same place that I was in another post. I'm when I go into
browse mode with the layout that contains the portal, the portal is just
a white box. Nothing can be entered into it.

An aside. The old  Borland database program, Reflex Plus, handled
many-to-many relationships for you. I assume there was a join table, but
it was hidden and the user didn't have to worry about it. 

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [next] | [standalone]

#2970

On 2018-01-21 21:55:21 +0000, Charles H. Sampson said:
> 
> I'm back to trying to understand the FMP way of implementing a
> many-to-many relationship. To do this, I'm using the example in the
> online FileMaker help. Three tables: Students, Classes, and Enrollments.
> Enrollments is the join table between Students and Classes.

I've never really like the "many-to-many" description because it's 
confusing and often incorrectly used.

In this example you're wanting to create enrolments from the Student 
Table, it's probably easier to think of it as two chain-connected 
relationships:

      - each student record is related to many enrolments (one-to-many),
and   - each enrolment record is related to one class record (one-to-one).




> As suggested, I have created a portal from the Students table to the
> Classes table, presumably through the Enrollments table. I have checked
> the box to allow creation of an Enrollments table by means of the
> relationship from Students to Enrollments. (I want to be able to create
> an enrollment by using the portal.) I have not checked the box to allow
> creation of a classes record by means of the relationship from
> Enrollments to Classes. (Students are not allow to sign up for
> non-existant classes, thereby creating the class.)
> 
> Now I'm at the same place that I was in another post. I'm when I go into
> browse mode with the layout that contains the portal, the portal is just
> a white box. Nothing can be entered into it.

It sounds like the Create Related Records option is turned on 
correctly, so make sure the fields in the Portal are using that same 
Relationship as the Portal itself, and they are all within the first 
Portal row in Layout Mode.

Without being able to see it, it's difficult to say what is wrong. You 
could always put the file in a Zip archive and upload it somewhere, 
then we can take a look.

Helpful Harry  :o)

[toc] | [prev] | [next] | [standalone]

#2971

On 2018-01-21 21:55:21 +0000, Charles H. Sampson said:
> 
> I'm back to trying to understand the FMP way of implementing a
> many-to-many relationship. To do this, I'm using the example in the
> online FileMaker help.
<snip>

I meant to say ... I haven't looked at any of the examples that came 
with the newer versions of FileMaker Pro, but the samples that came 
with the old versions were often horrible and even buggy, so I wouldn't 
be /that/ surprised if there is something wrong with the one you're 
using.

Helpful Harry   :o)

[toc] | [prev] | [next] | [standalone]

#2972

Op 21-1-2018 om 22:55 schreef Charles H. Sampson:
> I'm back to trying to understand the FMP way of implementing a
> many-to-many relationship. To do this, I'm using the example in the
> online FileMaker help. Three tables: Students, Classes, and Enrollments.
> Enrollments is the join table between Students and Classes.
> 
> As suggested, I have created a portal from the Students table to the
> Classes table, presumably through the Enrollments table. I have checked
> the box to allow creation of an Enrollments table by means of the
> relationship from Students to Enrollments. (I want to be able to create
> an enrollment by using the portal.) I have not checked the box to allow
> creation of a classes record by means of the relationship from
> Enrollments to Classes. (Students are not allow to sign up for
> non-existant classes, thereby creating the class.)
> 
> Now I'm at the same place that I was in another post. I'm when I go into
> browse mode with the layout that contains the portal, the portal is just
> a white box. Nothing can be entered into it.
> 
> An aside. The old  Borland database program, Reflex Plus, handled
> many-to-many relationships for you. I assume there was a join table, but
> it was hidden and the user didn't have to worry about it.
> 
> Charlie
> 

Charles,
Using a join table is different. Basically to join students to classes 
the join table functions as an enrolment table
But to see the students in a portal from classes there have to be two 
valid relations.
first from classes to enrolments (the join table)
and also from enrolments to students

I have found no other way then to script this.
When I am in the classes table and want to joint a student to current 
class I would you a script that guides me throught the process. Step one 
store the class_ID (in a global field). goto a search page for the 
student. Find the student (searching or scrolling or what ever). In the 
student-record hit a button for a new script that stores the student_id. 
Now go to the enrolment table, let the script create a new record, fill 
in the two ID's you have stored (also by script) and go back to the 
classes and the newly joined student will show up through a correctly 
set up portal.

Now there is quite a bit more involved in this. Like safety features 
that filter cancels and guides that lead you through the steps. Forcing 
to take an appropriate step each time. Also allowing to add new students 
and new classes midway through the process. It is perhaps not as clean 
as we would like, but it works flawlessly for me and I use this system 
often with join-tables.

-- 
Keep well, Ursus

[toc] | [prev] | [next] | [standalone]

#2977

On 2018-01-22 09:13:09 +0000, ursus.kirk@gmail.com said:

> Op 21-1-2018 om 22:55 schreef Charles H. Sampson:
>> I'm back to trying to understand the FMP way of implementing a
>> many-to-many relationship. To do this, I'm using the example in the
>> online FileMaker help. Three tables: Students, Classes, and Enrollments.
>> Enrollments is the join table between Students and Classes.
>> 
>> As suggested, I have created a portal from the Students table to the
>> Classes table, presumably through the Enrollments table. I have checked
>> the box to allow creation of an Enrollments table by means of the
>> relationship from Students to Enrollments. (I want to be able to create
>> an enrollment by using the portal.) I have not checked the box to allow
>> creation of a classes record by means of the relationship from
>> Enrollments to Classes. (Students are not allow to sign up for
>> non-existant classes, thereby creating the class.)
>> 
>> Now I'm at the same place that I was in another post. I'm when I go into
>> browse mode with the layout that contains the portal, the portal is just
>> a white box. Nothing can be entered into it.
>> 
>> An aside. The old  Borland database program, Reflex Plus, handled
>> many-to-many relationships for you. I assume there was a join table, but
>> it was hidden and the user didn't have to worry about it.
>> 
>> Charlie
> 
> Charles,
> Using a join table is different. Basically to join students to classes 
> the join table functions as an enrolment table
> But to see the students in a portal from classes there have to be two 
> valid relations.
> first from classes to enrolments (the join table)
> and also from enrolments to students
> 
> I have found no other way then to script this.
> When I am in the classes table and want to joint a student to current 
> class I would you a script that guides me throught the process. Step 
> one store the class_ID (in a global field). goto a search page for the 
> student. Find the student (searching or scrolling or what ever). In the 
> student-record hit a button for a new script that stores the 
> student_id. Now go to the enrolment table, let the script create a new 
> record, fill in the two ID's you have stored (also by script) and go 
> back to the classes and the newly joined student will show up through a 
> correctly set up portal.
> 
> Now there is quite a bit more involved in this. Like safety features 
> that filter cancels and guides that lead you through the steps. Forcing 
> to take an appropriate step each time. Also allowing to add new 
> students and new classes midway through the process. It is perhaps not 
> as clean as we would like, but it works flawlessly for me and I use 
> this system often with join-tables.

You don't need a Script, although whether or not that is more efficient 
is a different question.

If you're entering Enrolment records in a Portal, then you already have 
the IDs needed - one from the current record and one for the opposite 
side of the enrolment. To use your example, if entering enrolments via 
a Portal in the Classes Table, then FileMaker already has the current 
ClassID and you type the StudentID into one of the Portal Fields.

To make things easier for the user, the database often does not use 
visible / user-entered IDs, so instead you enter the student's *name* 
when creating the Enrolment record in the Portal. In that case, an 
Auto-enter option for the hidden StudentID can retrieve that data using 
a second Enrolments->Students Relationship based on the StudentName.

You can have a problem is there are students with the same name or one 
has a subset of another's name (e.g. "John Smith" and "John Smithson"). 
FileMaker can display an error message when such 'duplicate' named 
records are created, so the nuser can rename the new one (e.g. "John 
Smith 2" instead of "John Smith").

There also used to be a slight issue when using old versions of 
FileMaker and you changed the name (the ID wouldn't automatically 
change), but in newer versions the Auto-enter option can be set to 
automatically update (making it almost the same as a Calculation Field).

Helpful Harry   :o)

[toc] | [prev] | [next] | [standalone]

#2980

Charles H. Sampson <csampson@inetworld.net> wrote:

> I'm back to trying to understand the FMP way of implementing a
> many-to-many relationship. To do this, I'm using the example in the
> online FileMaker help. Three tables: Students, Classes, and Enrollments.
> Enrollments is the join table between Students and Classes.
> 
> As suggested, I have created a portal from the Students table to the
> Classes table, presumably through the Enrollments table. I have checked
> the box to allow creation of an Enrollments table by means of the
> relationship from Students to Enrollments. (I want to be able to create
> an enrollment by using the portal.) I have not checked the box to allow
> creation of a classes record by means of the relationship from
> Enrollments to Classes. (Students are not allow to sign up for
> non-existant classes, thereby creating the class.)
> 
> Now I'm at the same place that I was in another post. I'm when I go into
> browse mode with the layout that contains the portal, the portal is just
> a white box. Nothing can be entered into it.
> 
> ...

I think I might have this figured out. The "trick" (technique) is to
create new records by using a layout that shows records from the join
table — the Enrollments table in this example. That seems a little
unnatural but not entirely. I wanted to be able to sign up a student for
his classes; in other words, using a layout that  shows the record for
that student. It isn't entirely unnatural to say that I have to create
enrollment records instead, maybe all for one student, maybe for lots of
students.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2981

On 2018-02-03 19:36:39 +0000, Charles H. Sampson said:

> Charles H. Sampson <csampson@inetworld.net> wrote:
> 
>> I'm back to trying to understand the FMP way of implementing a
>> many-to-many relationship. To do this, I'm using the example in the
>> online FileMaker help. Three tables: Students, Classes, and Enrollments.
>> Enrollments is the join table between Students and Classes.
>> 
>> As suggested, I have created a portal from the Students table to the
>> Classes table, presumably through the Enrollments table. I have checked
>> the box to allow creation of an Enrollments table by means of the
>> relationship from Students to Enrollments. (I want to be able to create
>> an enrollment by using the portal.) I have not checked the box to allow
>> creation of a classes record by means of the relationship from
>> Enrollments to Classes. (Students are not allow to sign up for
>> non-existant classes, thereby creating the class.)
>> 
>> Now I'm at the same place that I was in another post. I'm when I go into
>> browse mode with the layout that contains the portal, the portal is just
>> a white box. Nothing can be entered into it.
> 
> I think I might have this figured out. The "trick" (technique) is to
> create new records by using a layout that shows records from the join
> table — the Enrollments table in this example. That seems a little
> unnatural but not entirely. I wanted to be able to sign up a student for
> his classes; in other words, using a layout that  shows the record for
> that student. It isn't entirely unnatural to say that I have to create
> enrollment records instead, maybe all for one student, maybe for lots of
> students.

I should have picked that up earlier when you originally said: "I have 
created a portal from the Students table to the Classes table".

It's not possible in *any* version of FileMaker to double-jump a 
relationship, and for this example you don't need to anyway. The whole 
reason for the Enrolments table is to store the enrolment records that 
link a Student to a Class.

Maybe it's due to the Americanism "sign up a student for a class", 
instead of "enrol a student in a class".

It's also why I don't like the terminology "Many to Many" because t's 
misleading and often used incorrectly. As I think I said in an earlier 
reply, this example database is really better thought of as:

     - One to Many: each student record is related to many enrolment records
and  - One to One:  Each enrolement record is related to one class record

That's why two separate Relationships have to be defined.

Helpful Harry  :o)

[toc] | [prev] | [next] | [standalone]

#2982

Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:

> On 2018-02-03 19:36:39 +0000, Charles H. Sampson said:
> 
> > Charles H. Sampson <csampson@inetworld.net> wrote:
> > 
> >> I'm back to trying to understand the FMP way of implementing a
> >> many-to-many relationship. To do this, I'm using the example in the
> >> online FileMaker help. Three tables: Students, Classes, and Enrollments.
> >> Enrollments is the join table between Students and Classes.
> >> 
> >> As suggested, I have created a portal from the Students table to the
> >> Classes table, presumably through the Enrollments table. I have checked
> >> the box to allow creation of an Enrollments table by means of the
> >> relationship from Students to Enrollments. (I want to be able to create
> >> an enrollment by using the portal.) I have not checked the box to allow
> >> creation of a classes record by means of the relationship from
> >> Enrollments to Classes. (Students are not allow to sign up for
> >> non-existant classes, thereby creating the class.)
> >> 
> >> Now I'm at the same place that I was in another post. I'm when I go into
> >> browse mode with the layout that contains the portal, the portal is just
> >> a white box. Nothing can be entered into it.
> > 
> > I think I might have this figured out. The "trick" (technique) is to
> > create new records by using a layout that shows records from the join
> > table ╉ the Enrollments table in this example. That seems a little
> > unnatural but not entirely. I wanted to be able to sign up a student for
> > his classes; in other words, using a layout that  shows the record for
> > that student. It isn't entirely unnatural to say that I have to create
> > enrollment records instead, maybe all for one student, maybe for lots of
> > students.
> 
> I should have picked that up earlier when you originally said: "I have
> created a portal from the Students table to the Classes table".
> 
> It's not possible in *any* version of FileMaker to double-jump a 
> relationship, and for this example you don't need to anyway. The whole
> reason for the Enrolments table is to store the enrolment records that
> link a Student to a Class.
> 
> Maybe it's due to the Americanism "sign up a student for a class", 
> instead of "enrol a student in a class".
> 
> It's also why I don't like the terminology "Many to Many" because t's
> misleading and often used incorrectly. As I think I said in an earlier
> reply, this example database is really better thought of as:
> 
>      - One to Many: each student record is related to many enrolment records
> and  - One to One:  Each enrolement record is related to one class record
> 
> That's why two separate Relationships have to be defined.

Thanks yet again for another tutorial on the Filemaker way of doing
things, a concept that I continue to struggle with. I'm being mislead,
at least in a Filemaker context, by my experience with Borland's Reflux
Plus, which implemented many-to-many relationships. I assume that they
did this by means of hidden join tables. Do you have any idea how common
this is in the database world?

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2983

On 2018-02-06 10:12:24 +0000, Charles H. Sampson said:
> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
>> On 2018-02-03 19:36:39 +0000, Charles H. Sampson said:
>>> Charles H. Sampson <csampson@inetworld.net> wrote:
>>>> 
>>>> I'm back to trying to understand the FMP way of implementing a
>>>> many-to-many relationship. To do this, I'm using the example in the
>>>> online FileMaker help. Three tables: Students, Classes, and Enrollments.
>>>> Enrollments is the join table between Students and Classes.
>>>> 
>>>> As suggested, I have created a portal from the Students table to the
>>>> Classes table, presumably through the Enrollments table. I have checked
>>>> the box to allow creation of an Enrollments table by means of the
>>>> relationship from Students to Enrollments. (I want to be able to create
>>>> an enrollment by using the portal.) I have not checked the box to allow
>>>> creation of a classes record by means of the relationship from
>>>> Enrollments to Classes. (Students are not allow to sign up for
>>>> non-existant classes, thereby creating the class.)
>>>> 
>>>> Now I'm at the same place that I was in another post. I'm when I go into
>>>> browse mode with the layout that contains the portal, the portal is just
>>>> a white box. Nothing can be entered into it.
>>> 
>>> I think I might have this figured out. The "trick" (technique) is to
>>> create new records by using a layout that shows records from the join
>>> table - the Enrollments table in this example. That seems a little
>>> unnatural but not entirely. I wanted to be able to sign up a student for
>>> his classes; in other words, using a layout that  shows the record for
>>> that student. It isn't entirely unnatural to say that I have to create
>>> enrollment records instead, maybe all for one student, maybe for lots of
>>> students.
>> 
>> I should have picked that up earlier when you originally said: "I have
>> created a portal from the Students table to the Classes table".
>> 
>> It's not possible in *any* version of FileMaker to double-jump a
>> relationship, and for this example you don't need to anyway. The whole
>> reason for the Enrolments table is to store the enrolment records that
>> link a Student to a Class.
>> 
>> Maybe it's due to the Americanism "sign up a student for a class",
>> instead of "enrol a student in a class".
>> 
>> It's also why I don't like the terminology "Many to Many" because t's
>> misleading and often used incorrectly. As I think I said in an earlier
>> reply, this example database is really better thought of as:
>> 
>>      - One to Many: each student record is related to many enrolment records
>> and  - One to One:  Each enrolement record is related to one class record
>> 
>> That's why two separate Relationships have to be defined.
> 
> Thanks yet again for another tutorial on the Filemaker way of doing
> things, a concept that I continue to struggle with. I'm being mislead,
> at least in a Filemaker context, by my experience with Borland's Reflux
> Plus, which implemented many-to-many relationships. I assume that they
> did this by means of hidden join tables. Do you have any idea how common
> this is in the database world?

No idea. Last time I used anything other than FileMaker Pro was decades 
ago, and even then it was only briefly mucking about with flat 
databases in things like 4th Dimension, FoxPro, etc. and the disgusting 
Access rubbish by Microsoft

For this particular "Students & Classes" example, you potentially don't 
need the extra Enrolments Table. You could relate each Student Record 
to many Classes directly. The main benefit of the Enrolments Table is 
so you can easily print off reports such as Class' Student Name Lists / 
Attendance Registers all in one go, rather than one at a time for each 
class from the Students Table. (Technically you could do Name Lists 
from the Classes Table by using an extra Field to gather all the names 
or by simply a printing the Portal, but printing Portals or long Fields 
isn't a great idea for an unknown number of rows or when going over 
multiple pages.)

Helpful Harry  :o)

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.filemaker

csiph-web