Open child form with filter based on one indexed, one non-indexed fields

[rmcgaffic]

How do you open a child form to display records that
match two different fields on the parent form?

This simple question has burned up most of my Saturday with no resolution, hence this posting.

A set has been defined between the parent and child tables for a 1:M relationship with the link of Topic_Id

I expected this to be somewhat straightforward, but after spending the this morning and afternoon experimenting, seek the guidance of someone who knows what they're doing.

Parent table

Topic_ID
Question_No
Topic_Memo

Child table

Topic_Id
Question_No
Question_Memo

Topic_Id is indexed in both tables.

Question_No is NOT indexed for either table. Only occasionally will the child records need to be selected based on a field other than Topic_Id.

When I press a button on the parent form I would like to open the child record with both the same Topic_Id and Question_No as the parent.

I'm using an Action Script to do this, so getting there HALF way is fairly simple.

Here's what I've entered for the Open Default Form or Browse Action Script:

Selection criteria "Records that match the value in a control on the current form or report" 
Show records where the value in the field TOPIC_ID
Matches the value in the control TOPIC_ID

The correct child records are displayed. So far, so good

But how do I select the one child record that has the same value of Question_No as the parent?

Is it possible to specify TWO selection criteria using the script genie, that is finding the records using the match on Topic_id, then using "Records selected fy a filter expression" to select from the returned child records the one form that also matches Question_No. Sounds like a reasonable approach, but I couldn't make it work.

So I guessed we're supposed to use the Records selected by a filter expression option.

I tried first just to filter the child records on one field, then on both fields. I never made it to second base.

In the Expression builder I created the following expression just using the F2 keys.

TblChild->Topic_id = Topic_id

Note that I allowed Alpha to generate the expression, but I thought it curious that there was no table reference on the right side of the equation.

The filter failed; all records were returned not just the child records corresponding to the parent's Topic_Id.

Next I edited Alpha's filter to the following

tblChild->Topic_Id = tblParent.Topic_Id

which resulted in the error message:
"Invalid filter expression: TblChild->Topic_id = tblParent.Topic_id"

So I figured that the normal way of specifying a table.field must not apply to these filters so I replaced the period with -> as follows:

tblChild->Topic_Id = tblParent->Topic_Id

and you guess it got another error message:
"Invalid filter expression TblChild->Topic_Id = tblParent->Topic_Id"

I'm running out of alternatives. Can someone help me see the error in my ways?

Bob McGaffic
Pittsburgh

[rmcgaffic]

Re: Open child form with filter based on one indexed, one non-indexed fields

There's got to be a better way.

I tried another approach and solved the problem although somewhat clumsily. I should would like to hear from forum members who see a cleaner way of doing this, without resorting to the use of intermediary variables.

Also I would still appreciate any insights as to why the filter expressions I used in my previous post all failed.

When you open a form, does Alpha Five open the new form (thus changing parentform) and then filter the records or does it select the records first by a find or a filter, then open the new form?

Here's what I did that worked:

I defined global variables for both
mTopic_Id
mQuestion_No

I then populated these variables on the parent form.

Next I used these variables to open the child form with Records Selected By a Filter Expression.

Here is the filter that worked:
tblChild->Question_No = Var->mQuestion_No .and. tblChild->Topic_Id = Var->mTopicId

Bob McGaffic
Pittsburgh, PA

[Tom Cone Jr]

Re: Open child form with filter based on one indexed, one non-indexed fields

Bob, give us some sample data. You ought to be able to do this without using global variables. -- tom

[G Gabriel]

Re: Open child form with filter based on one indexed, one non-indexed fields

If you want an approach that does not involve variables:
Link the parent to the child using an expression instead of a field.

The expression would be:
padl(Topic_Id,10, "0")+padl(Question_No,10, "0") ...if these fields are character, or:
padl(str(Topic_Id),10, "0")+padl(str(Question_No),10, "0") .. if both are numeric.

In the above expression, I used 10. Change as appropriate.

A form based on this set will show child records matching a parent with the same topic_ID and same Question_No

If these expressions seem unduly complicated: it's for a reason.
The reason?
As Jack Palance would say: That's for you to figure out.

Of course there is a myriad of other ways to do this. For now, let's start with the basics.

Attempting to answer the other questions without the context is crapshoot.

[rmcgaffic]

Re: Open child form with filter based on one indexed, one non-indexed fields

Tom, Attached is a test database of 1 parent record and 5 children records per your request. It is a test of your current event's knowledge.

G, I'll try your suggestion tonight.

Thanks to both of you.

Bob McGaffic
Pittsburgh

[Stan Mathews]

Re: Open child form with filter based on one indexed, one non-indexed fields

Not sure about the genie. Here's a one line inline xbasic solution.

[Tom Cone Jr]

Re: Open child form with filter based on one indexed, one non-indexed fields

And here's an approach using Action Scripting (though a tiny bit of inline xbasic was used, also.) -- tom


Later on ...Stan's approach sets a basequery in the called form. User cannot "ShowAll" records. This is probably a good thing in Robert's app. My approach does NOT set a basequery, but runs a simple query instead. ShowAll is active in the called form, using my approach.