Parent Child Issue

[RHoward3232]

Scenario: I created a one-to-many set where as �Subscribers� database is parent and �Subscriptions� database is the child. Databases are linked by �AcctNo� field.

Objective: Give the �YorN� field in the �Subscriber� database a �Y� value if a subscriber has a subscription expiring on a specific date (ExpDate) input with a dialog box.

Procedure: I designed an Update Records operation. It compares the child records �Enddate� field with the expiration date and assigns �Y� to the parent�s �YorN� field if found�
Field is YorN
Expression is �Y�
Condition is Var->ExpDate=Subscription->Enddate

Problem: The �YorN� field in the �Subscriber� database is valued at �Y� ONLY if the expiring subscription in the child database is the last child record entered for that subscriber. Since a subscriber may have subscriptions for several publications, the expiring subscription may not be the last on record.

In alpha 4, I used the following Global Update�
Field is YorN
If(Var->ExpDate=Subscription->Enddate, �Y�,YorN)
This leaves the YorN field at �Y� if a subsequent non-expiring subscription is encountered. I can�t make this work in Apha 5.

Thanks for any help.
Bob

[G Gabriel]

Re: Parent Child Issue

Are you saying: you want the �YorN� field in the �Subscriber� database to be "Y" if ANY of the subscriptions expired?

If so, use exist().

[RHoward3232]

Re: Parent Child Issue

yes, but the expiration dates are in the child subscription database... Where do I use exist() and what should I use for the 3 arguents? Sorry, I'm new to Alpha 5...

[G Gabriel]

Re: Parent Child Issue

You use exist instead of the if() for the Field YorN
You need to create an index for th echild table, using the expiration date
Then, in the YorN, you use the expression:
If (exist(..............),"Y", "YorN")
Check the help file for to learn how to use exist()

[Tom Cone Jr]

Re: Parent Child Issue

Bob,

I suggest you define an update operation for the Subscriber table. Set the YorN field to "N" for all records.

Then define a post operation in the Subscriptions table. Post a "Y" to the related record in the Subscriber table but only if the Enddate field in your Subscriptions table is equal to or earlier than the user supplied date.

Unless I'm missing something you don't need to use your set for this.

Example is attached. Unzip contents to an empty folder. Open form1 to find an explanation.

[RHoward3232]

Re: Parent Child Issue

The post records operation works well. Thanks for the help. I now can set the �YorN� field to �Y� for all subscribers with a subscription ending on the date that was input. I also use a second post records operation to null out the �YorN� field if the subscription has been renewed.

Now, I want to create a simple report that prints subscribers based on �Y� in the �YorN� field and �detail� only the subscriptions with the ending date that matches the variable date. For some reason, the detail�s filter does not recognize the variable date�s value and ALL of the subscriber�s subscriptions are detailed. To test this, I place the variable date field in the header of the report... it produces a blank field when the report is run. The post records operations recognize it, but not the report.

FYI: The variable date is input with an X dialog box when a button on a form is pushed. It is the first event, the date is input with a calendar and Global is selected.

Any ideas? Thanks in advance.
Bob

[Tom Cone Jr]

Re: Parent Child Issue

Bob, yes. Suggest you begin a new thread since the initial question has been solved. When you begin the thread, post a zipped copy of the database with instructions for us to follow in your footsteps in order to see the problem.