How to change year in a portal

I have made a portal with 24 statistic fields giving me the sales and the costs per month.
It works fine with a script making the search for each  month and writing the results in the 24 fields like :
Jan TotSales1  TotCosts1
Fev TotSales2  TotCosts2
and so on
In the search field of my script I have written : 1/1/2004...31/1/2004 for the first one and 1/2/2004...28/2/2004 for the second and so on
Unfortunately this synthesis is only valid for 2004 and I will have to change the script for 2005 , 2006 and so on.
Does somebody know  a way ( more simple than to duplicate the script 10/12 times for 10/12 years )  to allow the user just to change the year (in a Global field for instance) and get all the statistics for the requested year.
hainauxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
Instead of defining the date manually, compute it.
For instance, instead of 01/09/2004, use
"1/9/" & year (now) & "..." & (("1/10" & year (now)) - 1)
The second member is a trick to get the last day of the current mont by using 1st day of next month - 1
You need to make an exception for 31/12 and use year + 1 in this case (if month=12, etc...)

There is an easier way to summarize that:
create a field which the month of the record date
use a summary field base on totalsales using the month as sorting argument (recapitulatif in fr)
summary(totalsales;month)
sort per date
here you are
If you only need the result on paper, you can forget the summary field and make a layout with a sub-summary based on month, and putting the totalsales field in it. once sorted, it will print a summary of sales for each month (use preview to see it and do not forget to sort, otherwise :-(
0
hainauxAuthor Commented:
Thank you indeed my dear Lesouet ( BTW what is your first name ? )
I note what you say about the way to define each period of time and I will use it. (.... -1)
It is not exactly what I am looking for.
You will understand bettermy purpose when reading the beginning of my script below ( which works fine ) .
If I use [year(now)] I will only get the values of the current year but not the values of 1-2-3 previous years.
The portal is on the screen only  and the user should be able to check the totalsales of every month for a given year selected by him. 2003- 2002- 2004 and so on
What do you mean by : "create a field which (with) the month of the record date" in which format ? June- 06- 6 ??
I do not understand " Use a summary field base on TotalSales using .......argument
Do you mean a "rubrique statistique" If yes where is "recapitulatif"
Sorry but I am a littel bit confused

SCRIPT
Afficher toutes les fiches
Mode Recherche
Insérer texte [ Date F, “01/06/2003.. .30/06/2003”]
Sélectionner tout le contenu
Executer recherche
Restreindre l’ensemble trouvé
Afficher fiche/requête/page
[Premiere]
Copier [ TOTVAL]
Sélectionner tout le contenu
Coller [ TOTO1 F]
Sélectionner tout le contenu
Copier [ TOTPAY]
Sélectionner tout le contenu
CoIIer[TOTO1P]
Sélectionner tout le contenu
Afficher toutes les fiches
Mode Recherche
Insérer texte [ Date F, “01/07/2003.. .31/07/2003”]
Sélectionner tout le contenu
Executer recherche
Restreindre lensemble trouvé
Afficher fiche/req uête/page
[Premiere]
Copier [ TOTVAL]
Sélectionner tout le contenu
Coller [ TOTO2F]
Sélectionner tout le contenu
Copier [ TOTPAY]
Sélectionner tout le contenu
Coller [ TOTO2P]
Sélectionner tout le contenu
And so on for 12 months            -1-



SCRIPT
Afficher toutes les fiches

Mode Recherche

Insérer texte [ Date F, “01/06/2003.. .30/06/2003”]
Sélectionner tout le contenu

Executer recherche
Restreindre l’ensemble trouvé

Afficher fiche/requête/page
[Premiere]

Copier [ TOTVAL]
Sélectionner tout le contenu

Coller [ TOTO1 F]
Sélectionner tout le contenu

Copier [ TOTPAY]
Sélectionner tout le contenu

CoIIer[TOTO1P]
Sélectionner tout le contenu

Afficher toutes les fiches

Mode Recherche

Insérer texte [ Date F, “01/07/2003.. .31/07/2003”]
Sélectionner tout le contenu

Executer recherche
Restreindre lensemble trouvé

Afficher fiche/req uête/page
[Premiere]

Copier [ TOTVAL]
Sélectionner tout le contenu

Coller [ TOTO2F]
Sélectionner tout le contenu

Copier [ TOTPAY]
Sélectionner tout le contenu

Coller [ TOTO2P]
Sélectionner tout le contenu


And so on for 12 months            -1-
0
lesouefCommented:
ok... a bit too complex to explain in 2 mn. I am busy at the moment, I'll answer later on tonight.
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

hainauxAuthor Commented:
ok thanks.
In the meantime I have tried to search using "1/9/" &ANNEE(MAINTENANT) and also =="1/9/" &ANNEE(MAINTENANT)
 and also
"1/9/" &ANNEE(DateGlobal)
and in both cases I have been told that the date format is wrong. Why ?
In fact what I need is a way to change the YEAR in the formula of the script.
If the user could input a year i.e "2002" in a global date field called "DateGlobal" and if I could use this field in a formula for the searching the problem will be solved.
Something like :
Insérer texte [ Date F, “01/07/" &DateGlobal &...&"31/07/"&DateGlobal ]

Read you later.
Thanks again for your help
0
lesouefCommented:
depends on the field format used to search.
if the field is date type, you should use:
date ([month] ; [day] ; [year])
the way you did (I forgot to mention this) means you enter an ascii expression into a date field. When you type it by hand, since the field format is known by the user interface, it is "converted" automatically. When scripted, you must do this conversion yourself by using the date() function or calculating the date as a number of days since the 1/1/1900. same thing apply to hour which the amounts of seconds since midnight.
0
hainauxAuthor Commented:
sorry but I am not an expert like you and I do not understand. Could you give me some examples.
by hand : I want to search a period between 2 dates but I would like to take the year(date)
from a Global Field -> DateGlobal = "2002".
How can input that for [from 1st June+ [DateGlobal)...30th June + [DateGlobal]

by script :  same in ....... Insérer texte [ from 1st June+ [DateGlobal)...30th June + [DateGlobal]

Thanks
José
0
lesouefCommented:
the string to enter in the seach field if it is date formatted is like:
DATECHAINE(09;01;2004) & "..." & DATECHAINE(09;02;2004)
any of the numeric values above can be calculated also, like for instance, if replacing 2004 by the current year:
DATECHAINE(09;01;année(maintenant)) & "..." & DATECHAINE(09;02;2004)
if still unclear, let me know
About yr script above: summary function is:
RECAPITULATIF ( rubrique statistique ; rubrique de tri )
rubrique statistique = the stuff your want to summarize (totalsales in yr case) and
rubrique de tri = the criteria to summarize, ie the year in your case. This is why you need a field to reflect the year of the record.
year=année([the date field in yr database])
0
hainauxAuthor Commented:
It is clear but doesn't work.
When I replace in my above script
[Insérer texte [ DateF, “01/07/2003.. .31/07/2003”]
by
[Insérer texte [ DateF, DATECHAINE(07,01,2003) & "..." &DATECHAINE(07,31,2003)
I got the error message : "la valeur doit etre une date valable comprise entre l'an 1 et 3000 au format "25/12/1997"
I become creasy :-(
0
lesouefCommented:
you're right, I forgot, in search mode, an ascii expression must be entered.
so it must be either [Insérer texte [ DateF, “01/07/2003.. .31/07/2003”]
where some members can be replaced by a calculation. like:
Insérer texte [ DateF, “01/07/2003.. .31/07/" & année(maintenant)

or

DATECHAINE(DATE(09;2;2004) )& "..." & DATECHAINE(DATE(09;2;2004))

sorry for the mistake
0
hainauxAuthor Commented:
I think that we are going round in circles though we are close to the goal..
The only question is : Does it exist a way to replace in all above functions the member [year 2003] or any other year which is not YEAR[NOW] by an expression instead of writing 2003, 2002 or so.
For example a number included in a  global number field.
like this : YearRequested= 2002
Insérer texte [ DateF, “01/07/" & YearRequested.. .31/07/" & YearRequested)

Unfortunately this expresssion doesn't work.
I will try with DATECHAINE replacing 2004 by YearRequested.
Any idea ?


0
lesouefCommented:
almost correct, but YearRequested is a date field.
So make the global field a number field to use the expression the way it is now. (The way it is now, you mix an ascii expression with a date info)
And if you use datechaine(YearRequested), you get a complete date expression where you must extract the year from it.
As far as the year value is concerned, you use to a loop which use year (now), year (now) - 1, ... -untill -4 to avoid to specify 2000, 2001, 2002, 2003.
I have very little time at the moment.
In the worst case, you can send me a sample file, and I'll set it up for you this next week-end.
0
hainauxAuthor Commented:
OK. Understood.
In fact all this stuff can be summarize like this :
How to replace  in my script the sentence :

Insérer texte [ DateF, “01/07/2004.. .31/07/2004")

which works perfectly to search in the date field "DateF"

with a sentence in which the year [2004] can be replaced by something else input by the user in a global field.
That's it.

I have tried everything without success..
0
lesouefCommented:
I don't understand, I am using this everyday...
Can you send me yr data base by mail (empty it if data is confidential)? or post it somewhere where I can get it via http, or ftp?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hainauxAuthor Commented:
Hi
I have given up the way I tried to find a way and change completely the method which this time has been successfull>
For your info please find hereafter a summary of my new script
Quote
STATOI
Activer modèle [ Lignes]
Afficher toutes les fiches
Trier [ Ordre de tn : ID (Croissant)
Sans dialogue]
Afficher fiche/requête/page
[Premiere]
Insérer résultat du calcul [ TOTO6F, 0]
Sélectionner tout le contenu
Insérer résultat du calcul [ TOTO6P, 0]
Sélectionner tout le contenu
Boucle
Si [ MOlS(Date F), M06]  AND [ ANNEE (Date F), YEAR]       ##   M06 = 6  Global field  
                              ## YEAR = 2003 Global field  input by user  
Insérer résultat du calcul [ TOTO6F, TOTO6F + VAL]
Sélectionner tout le contenu
Insérer résultat du calcul [ TOTO6P, TOTO6P + TOT_P]
Sélectionner tout le contenu
Fin de si
Afficher fiche/req uête/page
Suivante, Fin de script après dernière]
Fin de boucle
Activer modèle [ StatistiquesOl
Unquote.
Any way thank you very much for your precious help.
Of course I grant you the points promised for that question.
Bye
0
lesouefCommented:
looks smarter. but there are some syntax errors which make me guess a few things.
Si [ MOlS(Date F), M06]  AND [ ANNEE (Date F), YEAR]  for instance does not work...
Also, you seem to loop through all records instead of finding the right ones and then summarizing, that will be slow. You could also avoid to use a global field for the year to be selected by asking the operator to enter it in a search field, you then modify his entry before executing the find, just a small trick.
But I still don't understand why you do not use the regular summarize functions instead fo this loop which will desperately slow if you have more than 1000 lines...
If you'd like to review this, I keep my proposal to have a look at it.
And thanks for points, one or 2 more questions, and I'll be above the 100000pts where I win... nothing!
0
hainauxAuthor Commented:
OK Thanks. I will revert to this next week.
0
hainauxAuthor Commented:
Si [ MOlS(Date F), M06]  AND [ ANNEE (Date F), YEAR]  for instance does not work...
It works..
instead of finding the right ones and then summarizing
You are probably right but I do not know how to do that.
I have posted in a file to your name "lesouef" all necessary files and you can download them at : ftp://hainaux.com 
You will also find a quick note in .doc
Thank you for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.