How to: Table browse based on related set?

[Blake]

I have a table to browse, and I want to be able to filter this browse based on a related set, or perhaps just browse the set. The situation is a set of contacts who belong from 0 to n groups. Parent table is base contact info, child table is a linking table with contact ID and group ID.

(The set is pictured in http://msgboard.alphasoftware.com/alphaphorum/read.php3?num=11&id=22007&loc=0&offset=0&sortby=lastreply&direction=desc&thread=22007)

The issues are these:

1. When unfiltered, each parent record should appear at least once, even if n is zero, i.e., the parent belongs to no groups. (If I browse on a set designed to only show parents that have children, I won't get these obviously.)

2. Filtered or unfiltered, each parent record should appear no more than once, regardless of how high n is. (If I browse on a set, this won't happen, because each parent will get n entries.)

3. But the browse should be filterable by group, which I don't see a way to if I build the browse on just the parent table.

I've thought about reversing the parent-child relationship, as discussed here before, but that wouldn't appear to help.

I'm sure this must be possible but am clueless. Help?

[forskare]

RE: How to: Table browse based on related set?

Blake,

I tried to go to the thread, but got this response:

Fatal error: Maximum execution time exceeded in ./abstract.php3 on line 163

Which I think means that it took too long to get there but it really wasn't an abnormal amount of time.

What do you mand my: >>I have a table to browse>and I want to be able to filter this browse based on a related set, or perhaps just browse the set

[Tom Cone Jr]

RE: How to: Table browse based on related set?

Blake,

If your set is the foundation for a saved browse layout, then you will always have as many occurrences of each parent table record as that record has linked children. In the set, each row in such a browse represents a virtual or composite record consisting of the parent table fields, and the linked child table fields.

Most would approach this a bit differently.

Create a form layout, not a browse layout.
Base it on the set.
Drop fields on the form, but only fields from the parent table.
Drop a browse object on the form, link it to the child table, and specify 'embedded'.

Now your user will see only one parent table record, but the fields for that record will be on the form, and not in a browse.

Now your child table fields are displayed in the embedded browse object.

-- tom

[Blake]

RE: How to: Table browse based on related set?

Thanks, Tom.

I know a form can be done that way, but you only see one parent record at a time with that. I want a browse. Now, I don't actually need to see any data from the child: the child data isn't really that important, except as it's used to fitler.

[Blake]

RE: How to: Table browse based on related set?

Is this an embedded browse based on a table or a set?

No, it's a browse on a form. It's not a child browse. That's easy enough. It's a parent browse (optionally) filtered by child criteria

You can use any criteria to filter the browse or form on which the browse is built.

I don't see how to filter a browse based on criteria from a set you're not using in that browse. That's why I asked.

Sorry, but I'm confused on points 1, 2, and 3

I'll try to be clearer.

-> When designing a set, I have two basic applicable options: Show only those records with a child; show all records, child or no. The problems with this are that I only ever want to show a parent record once (if no child filter is being used, or if it qualifies for the child filter) or not at all (if the parent record does not qualify). This apparent limitation makes a set impractical for this purpose. It's understandable in the sense that a one-to-many browse is going to naturally show more than one parent record. I believe this means I must dismiss using a set as a possibility.

--> Clearly, then, I need to design a browse just on the parent table, and yet find some way to filter the table based on the values in another table. I don't see how to do this, but I suspect it involves creating a list of (unique)IDs from my child table. In fact, I'm sure I could do it if I dynamically populated a table based on the current request, but that seems like the long way 'round.

I hope that clarifies.

Thanks in advance.

[Tom Cone Jr]

RE: How to: Table browse based on related set?

Blake,

If you put the set itself in the browse object I do not believe you can restrict the display of primary table records to show only one instance of each primary table record. You will get x entriesif the primary table record is linked to x children. This is how I understand sets work in Alpha Five.

You could, however, use a browse based on the primary table, alone, and could popup another form to show related child table records using the OnRowDblClick event of the browse, if that's what you're looking for.

-- tom

[forskare]

RE: How to: Table browse based on related set?

When you say you want a browse, are you talking about a brows created in the browse tab of the CP or an embedded browse on a form?

>>the child data isn't really that important, except as it's used to filter.

[JohnZaleski]

RE: How to: Table browse based on related set?

You should be able to do this without a set.Let's say your membership table has an index tag like:
pgroup = client_no + group_no

you could filter on the expression:
exist(client_no + var->vgroup,"membership.dbf","pgroup")

This should return a true condition if the parent is a member of the desired group in the variable.

[Blake]

RE: How to: Table browse based on related set?

If you put the set itself in the browse object I do not believe you can restrict the display of primary table records to show only one instance of each primary table record. You will get x entriesif the primary table record is linked to x children. This is how I understand sets work in Alpha Five.

I believe you are correct. Which leaves the option of browsing the parent table but filtering it externally.

You could, however, use a browse based on the primary table, alone, and could popup another form to show related child table records using the OnRowDblClick event of the browse, if that's what you're looking for.

It's not. I now at least can phrase this succinctly: I need to filter a table browse based on data in a related (but separate) table.

Thanks.

===Blake===

[Blake]

RE: How to: Table browse based on related set?

You should be able to do this without a set.Let's say your membership table has an index tag like:
pgroup = client_no + group_no

you could filter on the expression:
exist(client_no + var->vgroup,"membership.dbf","pgroup")

This should return a true condition if the parent is a member of the desired group in the variable.

I was talking with an old-time dBaser and he suggested something much like this (though with different syntax). This would definitely work for filtering on a single-group, yes!

Now, how to handle the situation where there's N groups active? I guess I could put =that= in a function, and loop through the desired groups.

Thank you very much! I think that oughtta work!

===Blake===

[Tom Cone Jr]

RE: How to: Table browse based on related set?

Blake,

You've probably already thought of this, but it would not be difficult to add a series of flag fields to each parent table record. When the new parent table record is entered all are set false. When a related entry is made to one of the group tables, the corresponding flag in the parent table could be raised. Your browse of the parent table could then use one or more filters to screen out entries that don't belong to one or more groups.

-- tom

[Blake]

RE: How to: Table browse based on related set?

Yeah, I thought of various schemes to put the data or tip-off data in the parent field. Not that one, exactly, but I thought of using a character string that was basically a list of the group IDs the contact was in. But any scheme like this gets really messy since the user can customize the groups freely.

It's also not very "R" and while I'm not an R-zealot, this is a situation where it really makes sense: any kind of array-based scheme is going to mean more programming and more fragile data.