I am creating a new database for our non-profit organization that
receives donations from different sources. I am wanting to have a
different table for each source but have the data be displayed in a
single portal. I have used in the past the same file to record the
donors but I thought there might be something additional in FM7
Developer that would allow me to arrange this database differently
(i.e. having the tables in a single file and more complex relationship
creation options.)

Any help or guidance is greatly appreciated!

Stephen W.

Stephen <stephenmw@verizon.net> wrote:

> I am creating a new database for our non-profit organization that
> receives donations from different sources. I am wanting to have a
> different table for each source but have the data be displayed in a
> single portal. I have used in the past the same file to record the
> donors but I thought there might be something additional in FM7
> Developer that would allow me to arrange this database differently
> (i.e. having the tables in a single file and more complex relationship
> creation options.)
> 
> Any help or guidance is greatly appreciated!
>

Err. This seems to be a really bad data structure.  Putting identical
data, such as would be kept for a wide range of sources, in duplicate
files, is always a poor idea. What if you keep adding sources? Are you
constantly going to be adding tables and diddling with relationships?
Better to add records.  Besides, in FM 7 a single relationship can still
only connect two Table Occurances, no more. 

But I'm a little confused by your terminology. Are sources the same as
donors? If so, the only need is a parent file called Sources (or Donors)
and a child file for donations, each with a SourceID. Then in the child
file you can do reports to show things like donations in a time period,
sorted by Source. Or all donations from a single source, sorted by date
and totaled.

If donors are different from sources, then you'll need either to mark
them in the main table as Donor or Source, or create two tables, Donor
and Source, with Donor ID and Source ID. Then creation Donations as a
join file with both Donor ID and Source ID in each record, to show both
links. The join file becomes the main reporting file, allowing the same
reports as above.

Lynn Allen
www.semiotics.com


FM 7 Tip of the Week: External authentication can permit IT departments
to create and manage user accounts which log into FM. Security becomes
the responsiblity of IT, not FM.   

lynn@NOT-semiotics.com (Lynn allen) wrote in message news:<1gg01f4.kvzyfoor4i5oN%lynn@NOT-semiotics.com>...
> Stephen <stephenmw@verizon.net> wrote:
> 
> > I am creating a new database for our non-profit organization that
> > receives donations from different sources. I am wanting to have a
> > different table for each source but have the data be displayed in a
> > single portal. I have used in the past the same file to record the
> > donors but I thought there might be something additional in FM7
> > Developer that would allow me to arrange this database differently
> > (i.e. having the tables in a single file and more complex relationship
> > creation options.)
> > 
> > Any help or guidance is greatly appreciated!
> >
> 
> Err. This seems to be a really bad data structure.  Putting identical
> data, such as would be kept for a wide range of sources, in duplicate
> files, is always a poor idea. What if you keep adding sources? Are you
> constantly going to be adding tables and diddling with relationships?
> Better to add records.  Besides, in FM 7 a single relationship can still
> only connect two Table Occurances, no more. 
> 
> But I'm a little confused by your terminology. Are sources the same as
> donors? If so, the only need is a parent file called Sources (or Donors)
> and a child file for donations, each with a SourceID. Then in the child
> file you can do reports to show things like donations in a time period,
> sorted by Source. Or all donations from a single source, sorted by date
> and totaled.
> 
> If donors are different from sources, then you'll need either to mark
> them in the main table as Donor or Source, or create two tables, Donor
> and Source, with Donor ID and Source ID. Then creation Donations as a
> join file with both Donor ID and Source ID in each record, to show both
> links. The join file becomes the main reporting file, allowing the same
> reports as above.
> 
> Lynn Allen
> www.semiotics.com
> 
> 
> FM 7 Tip of the Week: External authentication can permit IT departments
> to create and manage user accounts which log into FM. Security becomes
> the responsiblity of IT, not FM.

Thanks for the response Lynn!

I see what you mean. This is why I am trying to get all the structure
elements on paper first, before diving headlong into the solution. The
reason I separated was because one was for individuals and the other
donors were companies. I am keeping slightly different information on
both of them so I thought of separating it so as to have a number of
fields in both of them empty or extraneous when I am filling in the
data. aI could use different layouts but what if I flip through the
records? Then I could have an individual layout displaying partial
data it has in common with the companies (i.e. address fields, phone,
email, etc.)

This is a little more background and maybe Lynn or someone else could
provide some direction. Thanks again!

Stephen W.

stephenmw@verizon.net (Stephen) wrote in message news:<b62d3491.0406270549.3cb9e68d@posting.google.com>...
> lynn@NOT-semiotics.com (Lynn allen) wrote in message news:<1gg01f4.kvzyfoor4i5oN%lynn@NOT-semiotics.com>...
> > Stephen <stephenmw@verizon.net> wrote:
> > 
> > > I am creating a new database for our non-profit organization that
> > > receives donations from different sources. I am wanting to have a
> > > different table for each source but have the data be displayed in a
> > > single portal. I have used in the past the same file to record the
> > > donors but I thought there might be something additional in FM7
> > > Developer that would allow me to arrange this database differently
> > > (i.e. having the tables in a single file and more complex relationship
> > > creation options.)
> > > 
> > > Any help or guidance is greatly appreciated!
> > >
> > 
> > Err. This seems to be a really bad data structure.  Putting identical
> > data, such as would be kept for a wide range of sources, in duplicate
> > files, is always a poor idea. What if you keep adding sources? Are you
> > constantly going to be adding tables and diddling with relationships?
> > Better to add records.  Besides, in FM 7 a single relationship can still
> > only connect two Table Occurances, no more. 
> > 
> > But I'm a little confused by your terminology. Are sources the same as
> > donors? If so, the only need is a parent file called Sources (or Donors)
> > and a child file for donations, each with a SourceID. Then in the child
> > file you can do reports to show things like donations in a time period,
> > sorted by Source. Or all donations from a single source, sorted by date
> > and totaled.
> > 
> > If donors are different from sources, then you'll need either to mark
> > them in the main table as Donor or Source, or create two tables, Donor
> > and Source, with Donor ID and Source ID. Then creation Donations as a
> > join file with both Donor ID and Source ID in each record, to show both
> > links. The join file becomes the main reporting file, allowing the same
> > reports as above.
> > 
> > Lynn Allen
> > www.semiotics.com
> > 
> > 
> > FM 7 Tip of the Week: External authentication can permit IT departments
> > to create and manage user accounts which log into FM. Security becomes
> > the responsiblity of IT, not FM.
> 
> Thanks for the response Lynn!
> 
> I see what you mean. This is why I am trying to get all the structure
> elements on paper first, before diving headlong into the solution. The
> reason I separated was because one was for individuals and the other
> donors were companies. I am keeping slightly different information on
> both of them so I thought of separating it so as to have a number of
> fields in both of them empty or extraneous when I am filling in the
> data. aI could use different layouts but what if I flip through the
> records? Then I could have an individual layout displaying partial
> data it has in common with the companies (i.e. address fields, phone,
> email, etc.)
> 
> This is a little more background and maybe Lynn or someone else could
> provide some direction. Thanks again!
> 
> Stephen W.

Here is some more info that might help.

Let's say I have these DBs for keeping track of donors to our
organization. We have two types of donors, companies and individuals.
The information we are keeping on each of them is different so we
thought about using a separate file for each instead of a single table
with different layouts. We have another db for the donations which are
linked relationally to the individuals and companies. All donations
are in the same file. Herein is the problem. I can only have it relate
backwards to one file and not both of them obviously. Is there a way
to better handle this? I guess if I absolutely had to we could put the
individuals and companies in the same file but I would much prefer not
to.

Thanks,

Stephen W.

Stephen <stephenmw@verizon.net> wrote:

> I am keeping slightly different information on
> > both of them so I thought of separating it so as to have a number of
> > fields in both of them empty or extraneous when I am filling in the
> > data. aI could use different layouts but what if I flip through the
> > records? Then I could have an individual layout displaying partial
> > data it has in common with the companies (i.e. address fields, phone,
> > email, etc.)
> > 
> > This is a little more background and maybe Lynn or someone else could
> > provide some direction. Thanks again!
> > 
> > Stephen W.
> 
> Here is some more info that might help.
> 
> Let's say I have these DBs for keeping track of donors to our
> organization. We have two types of donors, companies and individuals.
> The information we are keeping on each of them is different so we
> thought about using a separate file for each instead of a single table
> with different layouts. We have another db for the donations which are
> linked relationally to the individuals and companies. All donations
> are in the same file. Herein is the problem. I can only have it relate
> backwards to one file and not both of them obviously. Is there a way
> to better handle this? I guess if I absolutely had to we could put the
> individuals and companies in the same file but I would much prefer not to.

Actually, you SHOULD keep them in the same file, because they're both
doing the same thing, creating donation records.  There is NO OVERHEAD
to having empty fields on a record.  In addition, I think you're going
to find that for address data, most fields are shared. Think of the ease
of mailings when everybody shares one file!

I would recommend you study up on a technique called "Visibility" or
"Invisible portals."  Briefly, for this you need 4 fields. 

One is a DonorType field, a text field with radio buttons to mark
Individual or Corporate donor.

Second is a Constant field, a number calc with a value of 1 for all
records.

Third and fourth are two flag fields, number calcs called 

fIndividual = Patterncount(DonorType, "Individual") and
fCorporate = Patterncount(DonorType, "Corporate).  Make these fields
indexed.

Make two relationships, one between the Constant and each of the flag
fields.

Place a portal on the screen from each relationship. Give each one row,
make them invisible by removing all lines and fills.  Place the fields,
NOT FROM THE RELATIONSHIP, but from the main file, on top of these
portals. (you might leave the portals visible, color them brightly for
testing, until you get placement right, then return to invisible)

Until and unless you make a choice in your DonorType field, these
additional fields on top of the invisible portals will remain invisible,
and not enterable. Once the choice is made, the correct fields will
appear.

The only restriction is that you cannot place these portals one on top
of another. Do it side by side.  All fields must be totally inside the
portal.

This way you don't need two layouts.

Leave the records in one file, and inherit the property of Individual or
Corporate donor down to the donation records. That way you can separate
your reports by type easily. 

This is really the best way to go. There are alternatives, but they
won't give you a very robust data structure.

Lynn Allen
www.semiotics.com


FM 7 Tip of the Week: External authentication can permit IT departments
to create and manage user accounts which log into FM. Security becomes
the responsiblity of IT, not FM. 

 

Lynn allen <lynn@NOT-semiotics.com> wrote:

> I would recommend you study up on a technique called "Visibility" or
> "Invisible portals."  Briefly, for this you need 4 fields.

Actually, after I posted this, I realized I had never done this in 7.
Report back and let us know if it works? :)

Lynn Allen

Yep -- this works in 7.  I'm not sure if I would use the constant 1 with 
this, though.  I used to use the constant, but (at least prior to 7) in 
a multiuser environment, the field/portal would flash a lot at random as 
(I presume) other people were doing things.

Instead, I would change the fIndividual and fCorporate fields to use the 
record's primary ID.  This seems to avoid the flash.  On the other hand, 
I haven't tried 7 in a multi-user environment with more than three 
users, so the flash might not be a problem in 7.

Your calcs would then look like:

fIndividual = case(Patterncount(DonorType, "Individual"), RecordID) and
fCorporate = case(Patterncount(DonorType, "Corporate), RecordID)

Then make the relationship from each of these go to the RecordID field.


Lynn allen wrote:

> Lynn allen <lynn@NOT-semiotics.com> wrote:
> 
> 
>>I would recommend you study up on a technique called "Visibility" or
>>"Invisible portals."  Briefly, for this you need 4 fields.
> 
> 
> Actually, after I posted this, I realized I had never done this in 7.
> Report back and let us know if it works? :)
> 
> Lynn Allen

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg              (818) 883-2846
FM Pro Solutions       Los Angeles, California
Associate Member, FileMaker Solutions Alliance

lynn@NOT-semiotics.com (Lynn allen) wrote in message news:<1gg42gh.1ef1alv1d1cqfqN%lynn@NOT-semiotics.com>...
> Stephen <stephenmw@verizon.net> wrote:
> 
> > I am keeping slightly different information on
> > > both of them so I thought of separating it so as to have a number of
> > > fields in both of them empty or extraneous when I am filling in the
> > > data. aI could use different layouts but what if I flip through the
> > > records? Then I could have an individual layout displaying partial
> > > data it has in common with the companies (i.e. address fields, phone,
> > > email, etc.)
> > > 
> > > This is a little more background and maybe Lynn or someone else could
> > > provide some direction. Thanks again!
> > > 
> > > Stephen W.
> > 
> > Here is some more info that might help.
> > 
> > Let's say I have these DBs for keeping track of donors to our
> > organization. We have two types of donors, companies and individuals.
> > The information we are keeping on each of them is different so we
> > thought about using a separate file for each instead of a single table
> > with different layouts. We have another db for the donations which are
> > linked relationally to the individuals and companies. All donations
> > are in the same file. Herein is the problem. I can only have it relate
> > backwards to one file and not both of them obviously. Is there a way
> > to better handle this? I guess if I absolutely had to we could put the
> > individuals and companies in the same file but I would much prefer not to.
> 
> Actually, you SHOULD keep them in the same file, because they're both
> doing the same thing, creating donation records.  There is NO OVERHEAD
> to having empty fields on a record.  In addition, I think you're going
> to find that for address data, most fields are shared. Think of the ease
> of mailings when everybody shares one file!
> 
> I would recommend you study up on a technique called "Visibility" or
> "Invisible portals."  Briefly, for this you need 4 fields. 
> 
> One is a DonorType field, a text field with radio buttons to mark
> Individual or Corporate donor.
> 
> Second is a Constant field, a number calc with a value of 1 for all
> records.
> 
> Third and fourth are two flag fields, number calcs called 
> 
> fIndividual = Patterncount(DonorType, "Individual") and
> fCorporate = Patterncount(DonorType, "Corporate).  Make these fields
> indexed.
> 
> Make two relationships, one between the Constant and each of the flag
> fields.
> 
> Place a portal on the screen from each relationship. Give each one row,
> make them invisible by removing all lines and fills.  Place the fields,
> NOT FROM THE RELATIONSHIP, but from the main file, on top of these
> portals. (you might leave the portals visible, color them brightly for
> testing, until you get placement right, then return to invisible)
> 
> Until and unless you make a choice in your DonorType field, these
> additional fields on top of the invisible portals will remain invisible,
> and not enterable. Once the choice is made, the correct fields will
> appear.
> 
> The only restriction is that you cannot place these portals one on top
> of another. Do it side by side.  All fields must be totally inside the
> portal.
> 
> This way you don't need two layouts.
> 
> Leave the records in one file, and inherit the property of Individual or
> Corporate donor down to the donation records. That way you can separate
> your reports by type easily. 
> 
> This is really the best way to go. There are alternatives, but they
> won't give you a very robust data structure.
> 
> Lynn Allen
> www.semiotics.com
> 
> 
> FM 7 Tip of the Week: External authentication can permit IT departments
> to create and manage user accounts which log into FM. Security becomes
> the responsiblity of IT, not FM.

Once again, thank you for your thorough description and help. I will
continue mapping out the solution and begin to implement the things
you mentioned. Thank you also to Howard for your input as well. It's
great to have others share their expertise for those of us who are
still learning. Thank you!

Sincerely,

Stephen W.