Is it possible with Reports
Quote from CSSystems on February 10, 2021, 2:18 amI have a membership program which contains Birthdays.
I do have a button to calculate the age of the member showing on the screen.
I would like to create a report which lists the name and age of members.
How do I create a report which calculates and show the current age of the member when I run the report?
I have a membership program which contains Birthdays.
I do have a button to calculate the age of the member showing on the screen.
I would like to create a report which lists the name and age of members.
How do I create a report which calculates and show the current age of the member when I run the report?
Quote from Vadim on February 10, 2021, 8:00 am@cssystems
Create a database with a table that has a column for name, a column for date of birth, and a column for age. With a search query, you can get a report. And you can use the show table command to display only the columns you want. Before displaying this report, you need to update the age data (if the program start date is not the same as the date of the previous program start).
Create a database with a table that has a column for name, a column for date of birth, and a column for age. With a search query, you can get a report. And you can use the show table command to display only the columns you want. Before displaying this report, you need to update the age data (if the program start date is not the same as the date of the previous program start).
Quote from CSSystems on February 10, 2021, 5:30 pmSo I guess I create a table to hold the information I want by copying Name, Birthday, etc from my main table.
Run a procedure (While .. Endwhile) on each record to calculate/recalculate age when I want a new report. Then run a report based on the Age table to get what I want.
Am I right?
So I guess I create a table to hold the information I want by copying Name, Birthday, etc from my main table.
Run a procedure (While .. Endwhile) on each record to calculate/recalculate age when I want a new report. Then run a report based on the Age table to get what I want.
Am I right?
Quote from Gaev on February 10, 2021, 5:53 pm@cssystems:
Run a procedure (While .. Endwhile) on each record to calculate/recalculate age when I want a new report. Then run a report based on the Age table to get what I want.
That would be the "simple brute-force" method ... if you have many records, it might take an unnecessarily long time.
Another option would be to specify a subroutine in your dbpOpenTable command ... this routine will be called every time there is a change in the "current record" i.e. every time you add, update or navigate to a different current-record ... the routine would only need to update the recalculated field for this one record.
@cssystems:
Run a procedure (While .. Endwhile) on each record to calculate/recalculate age when I want a new report. Then run a report based on the Age table to get what I want.
That would be the "simple brute-force" method ... if you have many records, it might take an unnecessarily long time.
Another option would be to specify a subroutine in your dbpOpenTable command ... this routine will be called every time there is a change in the "current record" i.e. every time you add, update or navigate to a different current-record ... the routine would only need to update the recalculated field for this one record.
Quote from Vadim on February 10, 2021, 9:46 pm@cssystems
You can add an age column to your main table. You can display only the columns you want for the report. The table can be the same.
To calculate age correctly, you can update the data in the "Age" column when you start the program by running the necessary subroutine, which will get age from the current date and day of birth from the database. You can put a restriction (logical condition) - to recalculate the age only if the date differs from the date of the previous check (in this case the recalculation will not be performed if you run the program two or more times a day).
You can use the asDateTime plugin (Andrey Solodyankin) to calculate the age.
You can add an age column to your main table. You can display only the columns you want for the report. The table can be the same.
To calculate age correctly, you can update the data in the "Age" column when you start the program by running the necessary subroutine, which will get age from the current date and day of birth from the database. You can put a restriction (logical condition) - to recalculate the age only if the date differs from the date of the previous check (in this case the recalculation will not be performed if you run the program two or more times a day).
You can use the asDateTime plugin (Andrey Solodyankin) to calculate the age.
Quote from CSSystems on February 11, 2021, 2:19 amWould the subroutine on the OpenTable trigger the subroutine as the Report is generated?
The restriction: How would I keep track of the date is what done so I can check it? Does the variable stay alive for the entire session?
Would the subroutine on the OpenTable trigger the subroutine as the Report is generated?
The restriction: How would I keep track of the date is what done so I can check it? Does the variable stay alive for the entire session?
Quote from Vadim on February 11, 2021, 8:16 am@cssystems
There is always more than one way. You can put the name of the age calculation subroutine in the third parameter of the dbpOpenTable command. This will cause the subroutine to run every time the table is opened (and also when the table is updated - @gaev said this point and it's in the plugin's help file).
I suggested putting a logical condition into the age update subroutine itself - so that the subroutine would only perform age recalculation if it hasn't already been calculated for the current date. In order not to do extra work. Although it's not necessary.
To track the date, you need to get the current date (from a global variable, for example, [DateShort]) and compare it to the date you want to keep between runs of the program. The date can be written to a plain text file, or to an ini-file, or to the same database file (e.g. add a "date of change" column), or in the Windows registry. This means that at the end of the age update subroutine, you need to put a command to write the date of the last change in age data to the file or the registry. This is the date and then compare the current date on the computer when you open the program.
If age in your program is displayed only in years, you can go even further, and update age only for those whose date of birth is the same day and month as the current date. This can be combined with birthday notification.
There is always more than one way. You can put the name of the age calculation subroutine in the third parameter of the dbpOpenTable command. This will cause the subroutine to run every time the table is opened (and also when the table is updated - @gaev said this point and it's in the plugin's help file).
I suggested putting a logical condition into the age update subroutine itself - so that the subroutine would only perform age recalculation if it hasn't already been calculated for the current date. In order not to do extra work. Although it's not necessary.
To track the date, you need to get the current date (from a global variable, for example, [DateShort]) and compare it to the date you want to keep between runs of the program. The date can be written to a plain text file, or to an ini-file, or to the same database file (e.g. add a "date of change" column), or in the Windows registry. This means that at the end of the age update subroutine, you need to put a command to write the date of the last change in age data to the file or the registry. This is the date and then compare the current date on the computer when you open the program.
If age in your program is displayed only in years, you can go even further, and update age only for those whose date of birth is the same day and month as the current date. This can be combined with birthday notification.
Quote from CSSystems on February 23, 2021, 2:54 amThank you for your help. I believe I have this working to my satisfaction.
I did have some trouble saving a string to a date variable until I remembered the 'old' ! in front of the string so it doesn't try to calculate i.e. Setvar "[DateField]" " ![2/22/2021]".
Thank you for your help. I believe I have this working to my satisfaction.
I did have some trouble saving a string to a date variable until I remembered the 'old' ! in front of the string so it doesn't try to calculate i.e. Setvar "[DateField]" " ![2/22/2021]".