Birthday Query

[Tedacria]

I am trying to build into my application a query which will extract forthcoming birthdays from the Date of Birth Field with the ability of inserting at the time of the query a specific number of days in which birthdays are due i.e. 14 or 30 or 90 days from the date of the query.

As my knowledge of Xbasic is virtually nil I hope there is someone out there that might be able to help me.

[Tom Cone Jr]

Suggest you experiment with query by form, then use Ctrl + I to examine the expression which Alpha Five develops to filter the records you specify.

Alternatively, play with query operations. let the genie help you build the filter, then study the resulting code.

-- tom

[Peter.Wayne]

Tom,
The genie will never be able to come up with the code needed to find the coming birthdays from the date of birth field.

First, you need a global function to determine the next birthday from the date of birth:

function next_bday as d(dob as d)
this_years=ctod(""+month(dob)+"/"+day(dob)+"/"+year(date()))
if this_years>date() then
    next_bday=this_years
else
    next_bday=\
    ctod(""+month(dob)+"/"+day(dob)+"/"+(year(date())+1))
end if
end function

Then you can write a script to look for everyone in your table with a birthday coming up. In this example, the table "patient" has a field "dob" containing the date of birth of each patient:

dim ndays as n
dim start as d
dim end as d

result=ui_dlg_box("Find Birthdays",<<%dlg%
Enter number of days ahead to look: [%v%2.3ndays];
<*Go?ndays\>0> <Cancel>;
%dlg%)
if result="Cancel" then
    end
end if

start=date()
end=start+ndays

filter="between(next_bday(dob)"+",{"+\
 dtoc(start)+"},{"+dtoc(end)+"})"

names=table.external_record_content_get("patient",\
"last_name-', '+trim(first_name)","",filter)

topten=word(names,1,crlf(),10)
ui_msg_box("topten",topten)
end

[Tom Cone Jr]

Dr. Wayne,

I see what you mean. The problem is more complicated than I initially thought. Thanks for thinking it through more completely and helping to get Jim back on track after my ill advised detour. -- tom

[Peter.Wayne]

If anyone actually uses the script, the next_bday function as written does not include birthdays occurring on "today." Someone whose birthday is today will be considered as having a next birthday in one year. If that is not what is desired, e.g., if persons with birthdays today are to be included in the selection, then the next_bday function should be changed accordingly:

function next_bday as d(dob as d)
this_years=ctod(""+month(dob)+"/"+day(dob)+"/"+year(date()))
if this_years>=date() then
    next_bday=this_years
else
    next_bday=\
    ctod(""+month(dob)+"/"+day(dob)+"/"+(year(date())+1))
end if
end function

The only change is in the "if" comparison to make it ">=" instead of just ">".

[Peter.Wayne]

Well, perhaps this is getting a little obsessive, but the next_bday function I presented will fail if someone was born on February 29th of a leap year. This function corrects that:

unction next_bday as d(dob as d)
m1=month(dob)
d1=day(dob)
y1=year(date())
y2=y1+1
if month(dob)=2 .and. day(dob)=29 .and. (.not. isleap2(y1)) then
    m1=3
    d1=1
end if
if month(dob)=2 .and. day(dob)=29 .and. (.not. isleap2(y2)) then
    m2=3
    d2=1
else
	m2=m1
	d2=d1
end if 

this_years=ctod(""+m1+"/"+d1+"/"+y1)
if this_years>date() then
    next_bday=this_years
else
	next_bday=ctod(""+m2+"/"+d2+"/"+y2)
end if
end function

and it uses the "helper" function of isLeap2():

FUNCTION isLeap2 AS L (yr AS N )
	isLeap2=(day(ctod("3/1/"+yr)-1)=29)
END FUNCTION

[masteralone]

Bravo Dr. Wayne!!!!! ;)

For years I have wondered why Selwyn has not included an age function in Alpha products. Perhaps you can nudge him to include one in the next version?

If someone simply wanted to create their own genealogy database, they might want to see what the current age is for a living member or even use it in calculations to determine the average life spans over a user specified period of time. For Doctors, might even lead to familial/genetic traits as I know of several that are heart related and show within a certain age range.

My two cents worth

Oran

[Tedacria]

First of all a big thanks to Peter for picking up my plea and to Oran I can only concur with his contribution. After all, one of the most common pieces of information held in any personnel database is the Date of Birth and is used for many different functions. Here's hoping

[Tom Cone Jr]

For years I have wondered why Selwyn has not included an age function in Alpha products. Perhaps you can nudge him to include one in the next version?

AGE()

(Has been in the product for a number of years now.)

-- tom

[Peter.Wayne]

Jim asked me (in a private message) to clarify how the different scripts are used. Here goes:
1) First, in the code editor, create the two functions, next_bday() and isLeap2(), and save them as global functions in your database.
2) Then the script containing the ui_dlg_box that asks for the number of days and does the query can be written as a new Xbasic script in the code editor and saved under some name of your own choosing, e.g., "BdayQuery". Remember in that script to change the name of the table from "patient" to whatever your table name is, and the field "dob" to whatever field contains the birthdate information.

Oh, yes. If you don't know how to compose scripts and functions in Alpha Five, then I know a good book that help you...

[JohnZaleski]

there is an age funtion
below is from the interactive window
john = {06/01/1951}

?john
= {06/01/1946}

?age(john)
= 55

[Mike Wilson]

cluckers about tackling school grade

OK, the birthday thing ya'll jumped on like an amusement park ride, but NADA one response for the question about a good approach to tackle the operation to update children's school grades which I posted.

http://msgboard.alphasoftware.com/al...ad.php?t=61642

OK, maybe a limp topic, yah, I admit, so I'll throw it out as to what I ended up with there, maybe way off base, but in case someone cares, as there is some relationship between these two...

dim democnt as N
democnt=tablecount("demographics",".T.")    'this estabishes the total records for the Waitdialog to set a percentage as it cycles

dim wtbox AS {WAITDIALOG}
dim cycle as N=0
dim tbl as p
dim cycle as N=0
wtbox.CREATE(1,"percent")

tbl = table.open("demographics")
query.filter = ""
query.order=""
query.options=""
xi=tbl.query_create()

tbl.fetch_first()
while .NOT. tbl.fetch_eof()
	cycle = cycle + 1
	bdate= tbl.birthdate
	familynumber = tbl.familynumber      ' whole number 
	friendnumber = tbl.friendnumber       ' family number + 0.01 is father, .02 is mother, and .03 and above children
	chnum=familynumber+.02                'this isthreashold for child number
	fgrade = tbl.adjschoolgrade        ' extra field to adjust for late schoo starts and hold backs
	tbl.change_begin()
 IF bdate <> {} then                         ' not everyone is willing to divulge this
	if friendnumber>chnum then
		if (date_format(date(),"MM-dd"))< "07-15" then
			tbl.schoolgrade=((year(now())-year(bdate))-6+fgrade)
			tbl.graduationyear=((year(bdate)+18-fgrade))
 		else
			tbl.schoolgrade=((year(now())-year(bdate))-5+fgrade)
			tbl.graduationyear=((year(bdate)+18-fgrade))
		end if
 	else
	 tbl.schoolgrade= 99
	 tbl.graduationyear=((year(bdate)+18+fgrade))
	end if 
 Else
  tbl.schoolgrade= -9               ' default for no school grade available
end if
tbl.change_end(.t.)
wtbox.SET_PERCENT(Var->cycle,Var->democnt)
tbl.fetch_next()
end while
xi.drop()
tbl.close()
wtbox.close()

[masteralone]

Grade in school

I didn't see the the post til now Mike. Also, from the other post and the info you supplied, I was assuming you were talking about the grades students receive in school. When I read through your script, I realized you were talking about the grade level in school.

To help, I think I would need to know the purpose of this script. Will it be used year end to update all students that move up a grade? Is it used for a student that has come into the school on a probationary basis that if they don't meet the criteria for the grade they are in they will be reverted to the previous grade? Does the school, school district, and state allow for skipping grades and this is what it's for? and so on

Lol, or is this for initial data entry for a new student?

[Mike Wilson]

School Grade levels

I didn't see the the post til now Mike. Also, from the other post and the info you supplied, I was assuming you were talking about the grades students receive in school. When I read through your script, I realized you were talking about the grade level in school.

To help, I think I would need to know the purpose of this script. Will it be used year end to update all students that move up a grade? Is it used for a student that has come into the school on a probationary basis that if they don't meet the criteria for the grade they are in they will be reverted to the previous grade? Does the school, school district, and state allow for skipping grades and this is what it's for? and so on

Lol, or is this for initial data entry for a new student?

Oran,

Thank you for your feedback. How funny it is how we 'believe' we see things. I am very thankful for your reply in bumping me to the perspectives you described.

The 'school grades' is about what year of schooling a child has attained, K-12, freshman, sophmore, etc. It needs updating at the end of each school year (hence the script containing if-then index of 7-15 (July, 15)). It resides within a large database that tracks families, their church and community relationships, and very specifically for evaluating the demographics of families for event planning and staffing. You have to now how many pounds of Tater-tots and applesauce you need for any given gathering, and how many high school graduations there are on the horizon.

I have received amazingly wonderful and highly appreciated help on this forum and I'm hoping nobody took any afront to my silly chiding. My experiences with A5 is that because it is such a robust product, in most circumstances, while a solutions finds it's way to me, mine might not be the most direct and efficient route, and the experience of others easily supercedes mine. But not always. I have found huge utility in A5_get _records_in_query(), and a5_external_records_contents_get() where others senior developers use it sparsly or differently.

http://msgboard.alphasoftware.com/al...ad.php?t=61426

It's all good...

Mike W

[Bob Arbuthnot]

Mike,

Enjoyed your "amusement park ride" analogy. I don't find one of your favorites,
a5_external_records_contents_get(), in the help files. Can you point me to it?

BA