Solved

How to change year in a portal

Posted on 2004-08-31
17
315 Views
Last Modified: 2010-04-27
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.
0
Comment
Question by:hainaux
  • 9
  • 8
17 Comments
 
LVL 28

Expert Comment

by:lesouef
ID: 11944852
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
 

Author Comment

by:hainaux
ID: 11945451
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
 
LVL 28

Expert Comment

by:lesouef
ID: 11950684
ok... a bit too complex to explain in 2 mn. I am busy at the moment, I'll answer later on tonight.
0
 

Author Comment

by:hainaux
ID: 11951224
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
 
LVL 28

Expert Comment

by:lesouef
ID: 11951845
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
 

Author Comment

by:hainaux
ID: 11952590
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
 
LVL 28

Expert Comment

by:lesouef
ID: 11955399
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
 

Author Comment

by:hainaux
ID: 11955769
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 28

Expert Comment

by:lesouef
ID: 11960943
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
 

Author Comment

by:hainaux
ID: 11961502
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
 
LVL 28

Expert Comment

by:lesouef
ID: 11961597
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
 

Author Comment

by:hainaux
ID: 11966717
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
 
LVL 28

Accepted Solution

by:
lesouef earned 250 total points
ID: 11970744
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
 

Author Comment

by:hainaux
ID: 11974710
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
 
LVL 28

Expert Comment

by:lesouef
ID: 11975073
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
 

Author Comment

by:hainaux
ID: 11975250
OK Thanks. I will revert to this next week.
0
 

Author Comment

by:hainaux
ID: 11977431
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now