1 Form containing more than 1 set

[bitwise]

Re: 1 Form containing more than 1 set

I have had a similar experience trying to get to grips with the restriction imposed by the simple set creation and, as a newbee, I am still at the experimental stage.
I have been used to Access and other 4GLs using SQL as the method of selecting the appropriate data for forms and sub-forms. With Access I would invariably need to use SQL commands to provide the data for sub-forms and dropdown fields, both of which would be tied or bound to data in the main form's table.
My first attempt at creating a desktop application in Alpha5 has had me stumped and I don't seem to be getting anywhere very fast.
My latest attempt has been to install the Visual Foxpro ODBC drivers (as recommended for ODBC access to the A5 DBF file tables - in another thread) and use an ODBC connection to the A5 tables in order to perform a SQL query on them. This I have then linked into a set to allow embedded browses of these sub-tables to be displayed on the form. Unfortunately, although it appears to work in theory it does not appear to work correctly and so is impeding any further progress along these lines.
The example I am trying to create is a Person table where I have a form with a tabbed control displaying the person's details on the first tab. Below this (or on a separate tab) I wish to display the person's children in a grid (browse control) and then on another tab display a grid of the person's siblings (denoting whether they are half or full brother/sister, along with other information from the sibling's Person record). This is all based on the one table (i.e. A linked list) which has a person_id field together with two other fields which contain the person_id of the person's mother and father respectively.
It does work, but not particularly cleanly. For a start, there is no access to the Alpha5 catalogue so the table names and column names and data types need to be pre-known when constructing the SQL and column names appear to be truncated to 10 characters. The data, although fine when looking at the ODBC Active Link tables, is getting repeated rows when shown in the browses (both the set's default form's browses and the form I have created). It appears to start off fine but as I move through the parent table's rows the sub-tables' rows get duplicated and on subsequent moves back to the same record (i.e. moving back to a particular record) more rows appear in the sub-table relating to that person than were there before.
It appears to be a bug.
Can anybody throw any light on a preferred, usable and clean way to achieve what I am trying to do?

[Davidk]

Re: 1 Form containing more than 1 set

I can always understand why people go after the one table approach... particularly since all persons - either siblings or children etc. are actually persons... but I can never see a good way of doing this.

I'd prefer to see a more relational approach... a persons table, a children table, a siblings table, etc.

The problems come in when a sibling becomes a person - a person with siblings, or a child becomes a person with children. But, this could be solved by adding additonal keys to the appropriate tables.

I can see using SQL to pull all the information together but then you're missing the strengths of the relational database... either in A5 or in Access. And, when the world changes and you have to modify your database things start to become a nightmare.

I've attached a really simple database that outlines what I think you want to do. No code, just A5 stuff.

[Al Buchholz]

Re: 1 Form containing more than 1 set

I'd prefer to see a more relational approach... a persons table, a children table, a siblings table, etc.

I don't think that is more relational. What definition of relational says that approach is more relational?

[Garry Flanigan]

Re: 1 Form containing more than 1 set

As food for thought, in one application I developed I used a "Name" table that had fields for say ID, first name and last name.

In the example in this thread, every "Name" could be a "Person", "Child" and/or "Sibling" and so the "Person", "Child" and "Sibling" tables just link "Name" records (using ID).

The same can occur with "Companies" that can be both a "Supplier and a "Customer".

This approach provides more normalized data and is therefore "more relational". Obviously you create an extra table, i.e., Names or Companies in each of the examples cited above but there are benefits from that point onwards (as well as drawbacks) which is why I offer it as food for thought.

[rmcgaffic]

Re: 1 Form containing more than 1 set

Guys, guys,

This is a data modeling issue, not an A5 problem. Once again, the Chapter People and Organizations in Silverton's Data Models book provides useful guidance.

Only one table is required: tblPerson.

Take a look at this:
http://www.durkinaddons.com/TheAssociator.aspx.

Should give you a pretty good idea of how to build all the relationships you need.

Some of your best leads can come from your
partner's neighbor's brother, for instance, or your 
assistant’s best friend’s boss. How do you keep track 
of complex relationships that involve multiple contacts
and don't fit neat categories? (Was the president of
Hot Big Company Joan’s son-in-law’s sister or Joan’s
son’s sister-in-law?)

The Associator addon for ACT! allows you to add an
unlimited number of free-form associations between
contacts by adding a new tab to the contact details 
window. This addon is embedded inside ACT!'s
easy-to-use interface, so there’s no need to run
multiple applications or launch external programs.
Once installed it seamlessly updates the contact 
details view by adding a new tab. This contact
management addon for ACT! is a must have for any
power user. 

The Associator addon for ACT! allows you design 
your own custom social networks by using tracking
two way associations that you can customize. It also 
enables you to track other details, such as which 
contact is the parent, and which one is the child 
or which ACT! contact is the client and which one 
is the customer. You can also customize networks
that apply specifically to your profession or business,
such as caregivers in a geriatric practice or regional 
distributors in retail want.

If Richard/Selwyn are entering the feature pack business, they would be wise to give us feature packs for all of the durkin addon's currently available for Act!.

And take a look at this:
http://durkincomputing.com/ACT-Addon...rViewPlus.aspx

Like I have said before, the world no longer needs a nickel cigar, it needs an A5 calendar like this ready to go.

Bob McGaffic
Pittsburgh, PA

[Garry Flanigan]

Re: 1 Form containing more than 1 set

Bob,

Regarding calendars, we know the Codejock Calendar can be used in Alpha (see Healthsoft Solutions app) and it is a relatively inexpensive addon. I have not tried to use it but you have previously mentioned it in glowing terms.

The lastest Alpha addons are at additional cost so why don't you just buy Codejock?

[bitwise]

Re: 1 Form containing more than 1 set

David, thank you. I had a look at your example and, although it presents the data as I wished, its fundamental flaw is that both the siblings' data and the childrens' data should be derived from the one table rather than specifying separate records for each relationship possible for each person. The information is already there... knowing a person's parents.

tblPerson:
person_id
name
dob
gender
father_id
mother_id

From just the above single table the necessary details can be derived from the selection criteria used.

The children for a particular person is based on a search for records that contain the person's person_id in either the father_id or mother_id field.

The siblings of a person is derived by a search for records where the father_id is the same as the person's father_id or the mother_id is the same as the mother_id of the person. If they are both the same, then it is a full sibling, otherwise if only one matches it is a half sibling (brother or sister depending on the sibling's gender).

Obviously one needs to take ensure the records selected are not the the person's own record and also that the matches are not on null values.

As I said, I have only been able to implement this using SQL (via Active Link Tables) as the basis of the selection as I cannot find a way within the Alpha5 interface to create the necessary sub-tables that can then be incorporated into a set (which I have called Family).

I started by exporting my table into a new Access MDB and used a Passive Link Table based on the necessary SQL query, which I then hooked into the set.. and this appeared to work fine except that I really wanted to work with the active Alpha5 DBF files, which is why I tried the Active Link Table approach using the Visual Foxpro ODBC driver (although as the query is based on linked tables (albeit the same) it is not updateable). The data is generated correctly but with the one problem, and that is successive movements within the parent table's records adds additional sets of the sub-table data... which I believe is a bug.

e.g.
person1
mother: none defined
father: none defined
children: person3, person4, person5
siblings: none
person2
mother: none defined
father: none defined
children: person4, person5
siblings: none
person3
mother: person1
father: none defined
children: none
siblings: person4 (half), person5 (half)
person4
mother: person1
father person2
children: none
siblings: person3 (half), person5 (full)
person5
mother: person1
father: person2
children: none
siblings: person3 (half), person4 (full)

Looking at person1 the children would show correctly but then if I move back to person1 after going through some of the other records in the set it might show the children for that person as:-
children: person3, person4, person5, person3, person4, person5

Is there a way to implement the above SQL logic using just Alpha5's features? I do not see how to implement a logical OR on two expressions based on two separate fields (columns) when defining a link within a set.

[Tom Cone Jr]

Re: 1 Form containing more than 1 set

Rod, I'm curious about your "fundamental flaw" assertion. It seems to me that while it might be possible to derive the desired relationships from records in a single table, what is it about the single table that makes it "fundamentally" better? Does a single table approach perform more efficiently? What "standard" are you measuring against? To me it seems that using a single table will necessarily impose significant pre-processing requirements in order to search for the related records. Can't that be reduced by using related tables?

And, I'm pretty sure "active link" tables were not designed to work with native Alpha Five tables. It's sounding like that's what you're attempting.

[rmcgaffic]

Re: 1 Form containing more than 1 set

Tom,

You can only lead a horse to water.

Rod's approach strikes me as flawed because it does not scale.

If all he wants to do is track mother and father relationships in a single table, well fine.

But we well know that the kinds of relationships between people and organizations is always changing, and I would think he would want to be able to accomodate such change.

I provided a reference to an Act! 2010 addon. Here's another to look at Centerbase.com. Centerbase allows you to link any person to another person, any person to an organization, any organization to an organization.

I'm pretty sure Goldmine takes the same approach, but I haven't looked at it in years.

Something like this requires only tblPerson and tblPersonRelationships as illustrated below, which shows Person_id, Role, Person_Id, Role:

Mary Godmother Tom Godson
Jill Aunt Tom Nephew
Harry Father Tom Son
James Father Harry Son

Gary,

I put the codejock calendar control on hold, and am pleased to report that others on this site (Don) have made very good progress with it.

I have turned my attention to a more critical issue, and activex control for a hierarchical grid. I've got much of it to work, but will shortly post a really baffling question caused by A5's non-zero based arrays.

My point with the calendar was that Alpha would be a way more powerful tool with the kind of add-ons that Act! 2010 offers. These are features that probably 80% of the Alpha developers out there would find useful.

Let me share a short conversation I had with Durkin Computing.
They have an add-on that allows users of Act! 2010 to add tables to their SQL Server database, which had me salivating. As it turns out, yes you can add any table you want to an application, but you have to link that table to one of these three tables: Person, Company, or Opportunity. The reason for this is that owner Sage doesn't want you the user to extended the functionality of Act!; they constrain add-on developers, because they want you to by their more advanced products, Saleslogix, SageCRM, at signficantly higher price tags.

Alpha several years ago abandoned its CRM development initiative with no explanation. So surely they were on the track for something like Act/Durkin. Wouldn't it be nice to break up what they did and offer it as Feature Paks to A5? I have also got to admit a fear that once Alpha saw what kind of functionality is now standard in the CRM marketplace, they realized that A5 was not up to the task. Come to think of it, whatever became of their subsequent "small business" software initiative?


Bob McGaffic
Pittsburgh, PA

[Mike Wilson]

Re: 1 Form containing more than 1 set

A few years ago I developed a very robust contact manager app that used 4 tables to set all these relationships. The 4 tables are Contact, Referral, Relations, and Genealogy. The set comes out to be 4 tables with the referral table being added twice, with the Geneology table being only a lookup table which contains all the combinations of relationships through 4 generations selected through a pictoral genealogy chart (see attached picture).

The premise is all persons (contacts) are part of a family, and have 2 components to their personal identification number (pqid), a family number and what position within the family they hold. The pqid is a combination of the family number and the family position number. Husband(father) is 00001.01, wife (mother) is 00001.02, first child is 00001.03, and so forth. When a child becomes an adult, they gain their own, new pqid, an update of the 3 tables (contacts, referral, relations) converts their old pqid to their new one, and the connections with each member of their primary family becomes a link through the relations table instead of a direct grouping in the contacts table. The referral table is a table for tracking who referred whom to whom, when and for what. The last is not necessary for a standard family model, so it could really be two tables in the set for that, but what I developed was for a church organization's "family model".

If desired by anyone I could go back and revisit that, pare it down, and post it as an example. It did reqiuire a fair amount of coding for updates, genealogy and referrals.

[bitwise]

Re: 1 Form containing more than 1 set

Tom,

Rod, I'm curious about your "fundamental flaw" assertion. It seems to me that while it might be possible to derive the desired relationships from records in a single table, what is it about the single table that makes it "fundamentally" better? Does a single table approach perform more efficiently? What "standard" are you measuring against? To me it seems that using a single table will necessarily impose significant pre-processing requirements in order to search for the related records. Can't that be reduced by using related tables?

The single table is all that is required. It is still relational (and scaleable, Bob) and does not duplicate data. The information is specific to the individual and the sub-tables can be easily derived (well they should be). Pre-processing should not be any different than referring to a separate table (in fact possibly less as a separate table would require many more records to define each persons relationships and would have to be updated separately instead of automatically being there)... although I may be wrong on this.

And, I'm pretty sure "active link" tables were not designed to work with native Alpha Five tables. It's sounding like that's what you're attempting.

You are right, that is what I am attempting. I have managed to resolve the issue I was having with duplicated entries in the embedded browse, with more added each time I moved to a different record - it would appear each time the browse is refreshed (when there are rows in the browse) instead of simply refreshing and recreating the sub-table data it would add another set to the rows already there. I have changed the table types of both the Children and Sibling ODBC connected tables to Passive Link Tables instead and all appears fine.

I would still like to know how I might achieve the SQL logic just using Alpha5's interface to the DBF tables - as opposed to having to resort to using SQL queries.

[Tom Cone Jr]

Re: 1 Form containing more than 1 set

Pre-processing should not be any different than referring to a separate table...

No, I think not. By definition the related child table will have already been "pre-filtered" or "pre-selected" so-to-speak. There's no need to step through all the single table's records to create a virtual table containing the related records for each primary key. This work is done already. If you have only a single table all the relationships have to be "rediscovered". But they already exist in the second table and need merely be read.