creating sets bug?

[moiola]

Hi everyone,

I have three tables:
1) Contact_information(id, name, phone, email, etc) - this one stores personal data
2) Organization (id, title, etc) - data about organizations
3) ConOrg (id_con, id_org) - this table stores data about contact belonging to different organizations (since one contact can participate in several organizations, this is a separate table)

Now I'm trying to create a set that will only show me those contacts who participate in organizations. In order to be able to search contacts by organization parameters I have to connect all three tables in a set.

1) I connect Contact_information to ConOrg requiring Contact_information.Id = ConOrg.id_con, Link_type is One-to_many, Include record from parent table = Only if a matching child record DOES exist. This works great.
2) I connect ConOrg to Organization requiring ConOrg.id_org = Organization.id, Link type is One-to-One, Include record from parent table = Only if a matching child record DOES exist. If I do that and try to open a set a5 consumes 100% CPU and that's all.

However if I do "Include record from parent table=Always", the set is opening normally.

I want to make "Include record from parent table=Only if a matching child record DOES exist" in order to filter contacts based on information stored in Organization table.

Why doesn't the set work with this settings? Is it a5 bug or I'm doing something incorrectly?

[Tom Cone Jr]

Re: creating sets bug?

Hello, Olga.

I don't have a lot of experience with the "include record from parent table only if a matching child DOES exist" option. It's possible that you have bumped into a bug, but there may be other explanations.

May I suggest you zip a copy of the database and attach it to a reply post in this thread? This will give others a chance to corroborate your findings, or perhaps to explain a different cause.

Later...

I built a two table set, using a one to one link, and don't seem to have any problem with the set design editor, or how a form behaves when based on the set. See the attached example. What's different in your case? For example, do you have no matching child table records?

-- tom

[moiola]

Re: creating sets bug?

Tom,

Thank you very much for your reply!
I do not have any problems when I connect two tables too... The problem occurs when I connect 3 tables (or more).

I have attached my database example to this post. This example DOES work (because tables ConOrg and Organization are linked with property "Include record from parent table"="Always"). But as soon as this link property is changed to "include record from parent table only if a matching child DOES exist" it is not possible to open a set any more...

Any ideas please?

I would really appreciate any help..

Thank you very much,
Olga

[Tom Cone Jr]

Re: creating sets bug?

Olga, I'll be surprised if that will ever work. Your desired set design gives Alpha Five conflicting instructions.

1) In the first link (between parent and child, one-to-many) you tell Alpha to include all the children that are linked to the parent.

2) in the second link (between child and grandchild, one-to-one) you want to tell Alpha to include only those children that have grandchildren.

Alpha may be hanging because it can't do both things your set design would require it to do.

These are inconsistent and, to my way of thinking, incompatible instructions. However, as I mentioned above I rarely use this option in my set designs. Maybe someone else here will have different ideas on this.

It will help if you tell us what it is that you want to accomplish with the "misbehaving" set. We may be able to suggest alternatives.

-- tom

[moiola]

Re: creating sets bug?

Hi Tom,

Thank you for your explanations very much. May be you can advise me the better way to construct a set if what I want to do is:

1) I'm going to use this set in a search engine. Example of the search: show the list of contacts who belong to specific organization
2) Contact_information table should be the main table in this set since I plan to add more tables to it (to search contacts based BOTH on activities they participated in and organizations they belong to).

My general idea was like that:
When I define a filter for organization table (i.e. organization.title="Hilton") I want to have in ConOrg only those organizations ids that reference to "Hilton" organization. And thus I will have only those contacts, who are attached to this organization... In other words, using this set I wanted to reach a result similar to the following SQL statement:

select distinct contact_information.id_number, contact_information.name
from contact_information, ConOrg, organization
where organization.title="Hilton"
and organization.id_number = ConOrg.id_num_org
and contact_information.id_number = ConOrg.id_num_con

Is that possible somehow?

Thank you very much for your answers, they are really helpful for me,
--
Olga

[Tom Cone Jr]

Re: creating sets bug?

My SQL is pretty weak, too <grin>, but I think you'd do it this way in Alpha Five:

Define a new set.
Make Organization table primary
Make ConOrg table child, link 1 to many on id_num_org
Make Contact_Information table grandchild, link 1 to 1 (id_num_con to id_number)

Open the default form for this new set.

Query the form for all Organization records where the "Name" field is "Bank of America".

In the browse object below you'll see only contacts that relate to that Organization.

Does that help?

Later...

If you're unfamiliar with how set based forms work, play with the default form for the set I've just described. Fetch the next Organization record. Notice that the list of linked contacts changes automatically. Fetch the previous, and see that it works both ways. Experiment and have fun. You may find that you don't have to run any queries at all.

-- tom

[moiola]

Re: creating sets bug?

Hi Tom,

Thank you very much for your help!
But my goal is to be able to search not only using data from "Organization" table, but also from other ones, i.e. also on "Activities", for example to have the list of all contacts who belong to specific organization and participated in specific activity... In this case making "Organizations" table a primary one is not an option from my point of view... : ((

Thanks,
Olga

[Tom Cone Jr]

Re: creating sets bug?

Olga, I guess I misunderstood you when you said:

I'm going to use this set in a search engine. Example of the search: show the list of contacts who belong to specific organization

The suggested set I described does exactly this.

Since I don't know what your search engine interface will look like, and since I don't know the format of the lists you wish to build, there's little more I can suggest. Good luck with your project.

-- tom