A way to determine what's missing from a Child Table

[Rudy16]

I would like to have a user enter a year and quarter via an Xdialog Box and then produce a Report of Residents whos dues are unpaid for that quarter.

The 2 tables are:
Resident_tbl contains...
Res_Id numeric 5 Linked to Res_Chld_Tbl 1 to many
Res_Name character 40
Res_Addr character 40

Resident_chld_tbl contains....
Res_Id_chld numeric 5 Linked to Resident_tbl
Dues_Year numeric 4
Dues_Quarter numeric 1 1,2,3, or 4
Dues_Amt numeric 8 6.2

Example:
There are 2 entries in the Resident_tbl. They are:
1 Joe Blow 16 Langley Toms River NJ 08757
2 Tom Smith 8 22nd St New York NY 56768

Therre are 4 entries in the Resident_Child_tbl for Resident 1. They are:
1 2009 3 36.00
1 2009 1 36.00
1 2008 4 36.00
1 2008 1 36.00

There are 5 entries in the Resident_Child_tbl for Resident 2. They are:
2 2009 1 36.00
2 2008 4 36.00
2 2008 3 36.00
2 2008 2 36.00
2 2008 1 36.00


If the user enters the year 2009 and quarter 2, the Report should look something like this:
Un-Paid Dues for Report for 2009, 2nd Quarter

1 Joe Blow 16 Langley Toms River, NJ 08757 2009 2 36.00
2 Toms Smith 8 22nd Street New York NY 56768 2008 2 36.00
______________
Total Un-Paid Dues 72.00

This seems extremely complicated to me. I posted it in this Forum because the activity here is much greater than the code forum and I'm hoping that one of you experts can help out.

Thanks,
Rudy

[Stan Mathews]

Re: A way to determine what's missing from a Child Table

It is much easier to report on something that exists than something that doesn't.

Add a field to Resident_Child_tbl. You could have a logical field for "paid" or you could have a numeric field for dues_paid. "Bill" every resident each quarter of each year and record payments. (Create a record for every resident showing the dues_amt and fill in the dues_paid when it is paid.)

With this set up it should be fairly easy to construct a report for records where the "paid" logical field is false or the dues_paid" numeric field is 0.

[Mike Wilson]

Re: A way to determine what's missing from a Child Table

I completely agree with Stan. I will add these notes. The example you gave has both residents at different addresses paying the same amount. If every resident has the same payment, all is easy, but what if the missed payment is a different amount? You can detect a record that shold be there but is not, but how would you expect the report to "know" how much is missing? And I will add my soapbox item. You have the resident id as a numeric field. Unless something is going to be used for mathmatical actions, character field is a better choice.

[Rudy16]

Re: A way to determine what's missing from a Child Table

Thanks to both of you for your quick response.

Although the amounts are the same in my example, they can be different. For example, if late, a 25.00 add on charge will be applied, thus making the dues amount due 51.00 instead of 36.00. There are also other assessments which I will not get into right now that can be tacked on.

Mike,
Resident_Id is an auto-increment field so that when I create a new resident, a unique Id will be created. I also do not have to know how much dues is required. The exception report will simply have the Name, Address, and Telephone number there for the Dues Administrator to contact. The administrator determines how much is due. My report example should have no dues amount shown and a telephone number on it. My fault.

Stan, your suggestion is simple and spot on. The only thing is I would have to come up with a routine to read each master record and create 4 child records for each year, each quarter and set the payed/not payed bit to "n". "y" = Paid. "n" equals Not Paid. I would like to assign it to a button so the user can enter the year he want's to set up and then run the routine on "Push" to create all the child records for each Master resident record. Example: The user enteres 2010 and 4 child records are created for each resident master record, 2010 for 1st qtr, 2010 for 2nd qtr, 2010 for 3rd qtr and 2010 for 4th qtr. I'm not able to write this script. I need an expert like yourself.

An additional field (pay or not payed) is perfect. This way I can check the quarter and year and if they match, check the payed or not payed field.

I'm sorry if I confused you with my example. Hope this clears things up.

The Un-Paid Dues Report would look something like this:

Un-Paid Dues for 2009, 2nd Quarter Report

1 Joe Blow 16 Langley Toms River NJ 08757 2009 2 732-554-1212
2 Toms Smith 8 22nd Street New York NY 56768 2009 2 732-244-8756


Thanks,
Rudy

[Stan Mathews]

Re: A way to determine what's missing from a Child Table

Unzip to a new location and open the database. Enter a year and an amount and press the bill button.

Examine the results.

I did not put any safety code to prevent billing the same year more than once.

[Rudy16]

Re: A way to determine what's missing from a Child Table

Stan,

Beautiful. Can I remove the "query.order" line from the script? The Resident Master recodrs are in a different sequence.

Also, it would be important to include a safety method to prevent from adding a year twice.

Thanks,
Rudy

[Stan Mathews]

Re: A way to determine what's missing from a Child Table

Done.

[Rudy16]

Re: A way to determine what's missing from a Child Table

Stan,
Copied and Pasted you examples to my test db on my desktop, modified your script and tested it out. I added a quarter variable also so a user could select a specific year and quarter. It worked great on my desktop.

I have a near working model on my laptop and typed in the below script into it.

I keep getting:
Error on Line 30, Column 1,
Error: Missing END IF Command.
when I try to save the script from the editor.

I don't see where the error is. Perhaps you can identify it.


tbl=table.current()
query.filter=".T."
query.order=""
query.description="temporaqry Query"
query.options="I"
tbl.query_create()
tbl.fetch_first()
dest=table.open("clients_child")
query.filter="Qtr_Year="+var->yr
query.filter="QTR="+var->Quarter
query.order=""
query.description="Temporaqry Query"
query.options="I"
ix=dest.query_create()
recs=ix.records_get()
if recs=0
while.not.tbl.fetch_eof()
dest.enter_begin()
dest.Child_Rec_No=tbl.Mst_Rec_No
dest.Qtr_Year=var->yr
dest.Qtr=var->Quarter
dest.Qtr_amt=var->due
dest.Paid="n"
dest.enter_end(.t.)
tbl.fetch_next()
end while
else
ui_msg_box("Oops","Buckets for "+alltrim(str(var->yr))+"have already been done.",UI_OK)
end if
dest.query_detach_all()
dest.close()

[Rudy16]

Re: A way to determine what's missing from a Child Table

Stan,

I removed "query.filter="QTR="+var->Quarter" from the script and it worked fine when entering a Year and Quarter however, after I run the script for 2009, 1st quarter and all entries are created correctly, when I try and run the script again for 2009, 2nd quarter, I hit the UI_Msg because I guess the year was already created.

How can the script be modofied to Check for the year and quarter instead of just the year?

Please advise.

Thank you,
Rudy

[Stan Mathews]

Re: A way to determine what's missing from a Child Table

The script (on the button) is designed to create the entire billing for the next (designated) year. I suggest you manually create the necessary records for 2009 and proceed from that point.

I could probably modify the script but it would be at least Wednesday of next week.

[Rudy16]

Re: A way to determine what's missing from a Child Table

Thanks Stan. I'll manually create them for now.

[Rudy16]

Re: A way to determine what's missing from a Child Table

Stan,

It's Wednesday and I thought I'd ask if you could look at the encloses script that I used to create child records based on a year and quarter variable accepted from an x-dialog form. It creates a child record for every parent record in the table for a specific year and quarter.

There is no code to check to see if a year and quarter has been run before. The variables accepted are yr and Quarter. While this works fine, I'd like to have a check coded in the script that checks to make sure a year and quarter can only be run once.

Can you review this for me and assist.

Thanks,
Rudy


'Date Created: 29-Jun-2009 07:20:49 AM
'Last Updated: 29-Jun-2009 07:27:03 AM
'Created By : Rudy
'Updated By : Rudy
tbl = table.current()
query.filter = ".T."
query.order = ""
query.description = "Temporary Query"
query.options = "I"
tbl.query_create()
tbl.fetch_first()
dest = table.open("clients_child")
while .not. tbl.fetch_eof()
dim p3 as waitdialog
p3.create(2,"repeating")
p3.set_bottom_message("hello. i am working...")
dest.enter_begin()
dest.Chld_Rec_No = tbl.Mst_Rec_No
dest.Qtr_Year = var->yr
dest.Qtr = var->Quarter
dest.Qtr_amt = var->due
dest.Paid = "n"
dest.enter_end(.t.)
tbl.fetch_next()
end while
p3.close()
dest.query_detach_all()
dest.close()

[Stan Mathews]

Re: A way to determine what's missing from a Child Table

Try this.

tbl = table.current()
query.filter = ".T."
query.order = ""
query.description = "Temporary Query"
query.options = "I"
tbl.query_create()
tbl.fetch_first()
dest = table.open("clients_child")
query.filter = "Qtr_Year = "+var->yr +".and. Qtr = "+ var->Quarter
query.order = ""
query.description = "Temporary Query"
query.options = "I"
ix = dest.query_create()
recs = ix.records_get()
IF recs = 0
	WHILE .not. tbl.fetch_eof()
		dim p3 as waitdialog
		p3.create(2,"repeating")
		p3.set_bottom_message("hello. i am working...")
		dest.enter_begin()
		dest.Chld_Rec_No = tbl.Mst_Rec_No
		dest.Qtr_Year = var->yr
		dest.Qtr = var->Quarter
		dest.Qtr_amt = var->due
		dest.Paid = "n"
		dest.enter_end(.t.)
		tbl.fetch_next()
	END WHILE
ELSE
	ui_msg_box("Oops","Already created that year and quarter.")
END IF
p3.close()
dest.query_detach_all()
dest.close()