Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to create weekly report 2
- Thread starter pt2huynh
- Start date
-
2
- #2
Hi JeanW.
This thread is a year old so I doubt you will get it but let me take a shot. This is my first full day with FileMaker Pro and I am trying to create a layout that will show me all records for the current week.
I have defined two a field as you suggested called 'week number' and it calculates the number for me just fine.
Where I am is stuck is in trying to create a script that will return all records where the 'Date' field = 'Week Number'. Can you please help me out with this?
Thank you,
Kevin
This thread is a year old so I doubt you will get it but let me take a shot. This is my first full day with FileMaker Pro and I am trying to create a layout that will show me all records for the current week.
I have defined two a field as you suggested called 'week number' and it calculates the number for me just fine.
Where I am is stuck is in trying to create a script that will return all records where the 'Date' field = 'Week Number'. Can you please help me out with this?
Thank you,
Kevin
Several ways to go...
If you have a 'weeknumberfield' = WeekOfYear(date), you can search on this field.
Something along these lines:
Go to layout (yourLayout)
Enter Find mode
Insert from Index (your weeknumberfield)
Perform find (replace found set)
Tweak your script to show one record if one record is found, what to do if no records, what to do if multiple records etc.
An other way to go, if you want to find 'dates' is a calculation field with a script, something along these lines (if you have a zc_creationDate = date field):
zc_creationDate_cti - calculation - text =
Case(
zc_creationDate = Status( CurrentDate);"Today";
zc_creationDate = Status( CurrentDate) - 1;"Yesterday";
zc_creationDate = Status( CurrentDate) - 2;"Last " & DayName(zc_creationDate);" "
)
& "¶" &
Case(
WeekofYear(zc_creationDate) = WeekofYear(Status( CurrentDate)) and Year(zc_creationDate) = Year(Status( CurrentDate)); "This week";
WeekofYear(zc_creationDate) = WeekofYear(Status( CurrentDate) - 7) and Year(zc_creationDate) = Year(Status( CurrentDate));"Week before";" "
)
& "¶" &
Case(
Month(zc_creationDate) = Month(Status( CurrentDate)) and Year(zc_creationDate) = Year(Status( CurrentDate));"Actual month";
Month(zc_creationDate) = Month(Status( CurrentDate) - 7) and Year(zc_creationDate) = Year(Status( CurrentDate));"Previous month";" "
)
& "¶" &
Case(
Year(zc_creationDate) = Year(Status( CurrentDate));"Current year";
Year(zc_creationDate) = (Year(Status( CurrentDate)) - 1);"Year before";
"Prior to last year"
)
A script Find that looks in the list of valid dates, and finds the records that match the selected dat or period.
Set Error Capture (on)
Allow User Abort (off)
Freeze window
Enter find mode
Go to layout (original layout)
Insert from index (zc_creationDate_cti)
If (isEmpty(zc_creationDate_cti)
#No item selected
Enter browse mode
Go to layout (original)
Halt script
End if
Perform find (replace found set)
Here you can insert some other steps, like trigger the possible errorcode and actions to do if...
Tweak the steps to go to the appropriate layout when you have 1 or more or none records....
HTH
JW
If you have a 'weeknumberfield' = WeekOfYear(date), you can search on this field.
Something along these lines:
Go to layout (yourLayout)
Enter Find mode
Insert from Index (your weeknumberfield)
Perform find (replace found set)
Tweak your script to show one record if one record is found, what to do if no records, what to do if multiple records etc.
An other way to go, if you want to find 'dates' is a calculation field with a script, something along these lines (if you have a zc_creationDate = date field):
zc_creationDate_cti - calculation - text =
Case(
zc_creationDate = Status( CurrentDate);"Today";
zc_creationDate = Status( CurrentDate) - 1;"Yesterday";
zc_creationDate = Status( CurrentDate) - 2;"Last " & DayName(zc_creationDate);" "
)
& "¶" &
Case(
WeekofYear(zc_creationDate) = WeekofYear(Status( CurrentDate)) and Year(zc_creationDate) = Year(Status( CurrentDate)); "This week";
WeekofYear(zc_creationDate) = WeekofYear(Status( CurrentDate) - 7) and Year(zc_creationDate) = Year(Status( CurrentDate));"Week before";" "
)
& "¶" &
Case(
Month(zc_creationDate) = Month(Status( CurrentDate)) and Year(zc_creationDate) = Year(Status( CurrentDate));"Actual month";
Month(zc_creationDate) = Month(Status( CurrentDate) - 7) and Year(zc_creationDate) = Year(Status( CurrentDate));"Previous month";" "
)
& "¶" &
Case(
Year(zc_creationDate) = Year(Status( CurrentDate));"Current year";
Year(zc_creationDate) = (Year(Status( CurrentDate)) - 1);"Year before";
"Prior to last year"
)
A script Find that looks in the list of valid dates, and finds the records that match the selected dat or period.
Set Error Capture (on)
Allow User Abort (off)
Freeze window
Enter find mode
Go to layout (original layout)
Insert from index (zc_creationDate_cti)
If (isEmpty(zc_creationDate_cti)
#No item selected
Enter browse mode
Go to layout (original)
Halt script
End if
Perform find (replace found set)
Here you can insert some other steps, like trigger the possible errorcode and actions to do if...
Tweak the steps to go to the appropriate layout when you have 1 or more or none records....
HTH
JW
Hi JW,
I have added the field WeekNumber into my layout, went into Find and did an Insert from Index. This brought up the entries in my WeekNumber field. I chose one and clicked Find and it worked properly.
What I would like to do is not have to manually enter the week number there. I would like to have that field always show the current week. Is that possible?
Thank you for all your help.
Kevin
I have added the field WeekNumber into my layout, went into Find and did an Insert from Index. This brought up the entries in my WeekNumber field. I chose one and clicked Find and it worked properly.
What I would like to do is not have to manually enter the week number there. I would like to have that field always show the current week. Is that possible?
Thank you for all your help.
Kevin
I created a script. For the steps I did:
Go to Layout ["My Layout"]
Enter Find Mode [Pause]
Set Field [My Table:
ate]
I am not sure how to do the step you mentioned:
Set field (your weeknumberfield);WeekOfYear(statusCurrentDate)
Sorry I'm so weak at this!
Thanks,
Kevin
Go to Layout ["My Layout"]
Enter Find Mode [Pause]
Set Field [My Table:
ate]I am not sure how to do the step you mentioned:
Set field (your weeknumberfield);WeekOfYear(statusCurrentDate)
Sorry I'm so weak at this!
Thanks,
Kevin
Let's go a few steps back....
To have a number of the week from a datefield, you have to enter that value in a weeknumberfield.
So first you need a field weeknumber = date.
Weeknumber = calc = WeekOfYear(yourDateField)
This will generate a number, which is the week of the year the date falls in.
You want to find the 'currentWeeknumber' value from a date field.
So you have to search on the weeknumberfield, which have the value of 'this weeknumber'.
When you set the weeknumberfield to weeknumber = yourdatefield, all those values from this week will be the same in the weeknumberfield (which is, I think week 20)
So you have to search for the value 20 (weekOfYear = 20) in the weeknumberfield.
You can find this value by setting the searchvalue for the weeknumberfield in Findmode to WeekOfYear(statusCurrentDate). Now you have a dynamic findsystem, no need to enter manually the weeknumbervalue....
Because statusCurrentDate = today = currentweeknumber.
You will find all the records with the currentweeknumber = 20.....
HTH
To have a number of the week from a datefield, you have to enter that value in a weeknumberfield.
So first you need a field weeknumber = date.
Weeknumber = calc = WeekOfYear(yourDateField)
This will generate a number, which is the week of the year the date falls in.
You want to find the 'currentWeeknumber' value from a date field.
So you have to search on the weeknumberfield, which have the value of 'this weeknumber'.
When you set the weeknumberfield to weeknumber = yourdatefield, all those values from this week will be the same in the weeknumberfield (which is, I think week 20)
So you have to search for the value 20 (weekOfYear = 20) in the weeknumberfield.
You can find this value by setting the searchvalue for the weeknumberfield in Findmode to WeekOfYear(statusCurrentDate). Now you have a dynamic findsystem, no need to enter manually the weeknumbervalue....
Because statusCurrentDate = today = currentweeknumber.
You will find all the records with the currentweeknumber = 20.....
HTH
Excellent information. I understand what we want to do.
I have a field "Week Number" defined that is working fine.
My script now reads exactly (it is for my father's bar):
Go to Layout ["Show Week"]
Enter Find Mode [Pause]
Set Field [O'Brien's Bar::Week Number; WeekOfYear(O'Brien's Bar::Week Number)]
Perform Find/Replace
Is this right so far?
I do not know where to put the "WeekOfYear(statusCurrentDate)". I get an error everytime when using that syntax in the calculation.
Thank you for all of your help.
Kevin
I have a field "Week Number" defined that is working fine.
My script now reads exactly (it is for my father's bar):
Go to Layout ["Show Week"]
Enter Find Mode [Pause]
Set Field [O'Brien's Bar::Week Number; WeekOfYear(O'Brien's Bar::Week Number)]
Perform Find/Replace
Is this right so far?
I do not know where to put the "WeekOfYear(statusCurrentDate)". I get an error everytime when using that syntax in the calculation.
Thank you for all of your help.
Kevin
Almost there...
Basically there's no need for the pause step,
Status function is a FM 6 syntax, which is a Get function in FM 7.
I don't have a FM 7 version right here, so check the functions to find the right one.
You want always the current week, that why you have to use the currentDate into the weeknumberfield.
The WeekOfYear together with the currentDate will give you the current weeknumber, the one you want to find/search for...
HTH
Basically there's no need for the pause step,
Status function is a FM 6 syntax, which is a Get function in FM 7.
I don't have a FM 7 version right here, so check the functions to find the right one.
You want always the current week, that why you have to use the currentDate into the weeknumberfield.
The WeekOfYear together with the currentDate will give you the current weeknumber, the one you want to find/search for...
HTH
Hi Jean,
Your solution worked great! I just didn't understand it at first being a total newbie. I went out and got a book, read the chapter on scripting and then was able to figure out your method and it does exactly what I want.
Thank you very much! You have been a huge help!
Kevin
Your solution worked great! I just didn't understand it at first being a total newbie. I went out and got a book, read the chapter on scripting and then was able to figure out your method and it does exactly what I want.
Thank you very much! You have been a huge help!
Kevin
When there are no records for the current week I get an error. How can I suppress the error or take another action such as a messagebox? Here is my script:
Go to Layout ["Weekly Report"]
Enter Find Mode []
Set Field [O'Brien's Bar::WeekNumber; WeekOfYear ( Get ( CurrentDate ))]
Perform Find []
Thank you for all your help!
Kevin
Go to Layout ["Weekly Report"]
Enter Find Mode []
Set Field [O'Brien's Bar::WeekNumber; WeekOfYear ( Get ( CurrentDate ))]
Perform Find []
Thank you for all your help!
Kevin
This is part of my post dd 16 05: 'Tweak your script to show one record if one record is found, what to do if no records, what to do if multiple records etc.'
Start your script with 'Set Error Capture - On'
It will suppress the FM error messages (called error trapping) and stores the error number in memory temporarily.
Use this script step whenever you want to capture a FM generated error and perform your own sequence of steps.
After your perform find you can go different ways, depend on the result.
Add a few if statements, after your perform find scriptstep, to tell FM what to do when....
1. If no record found the error number is 401.
The scriptstep will be:
If (Status(CurrentError) = 401
Show Message ('No records were found')
Provide buttons to go to browse or search again, and use the
Status(CurrentMessageChoice) to act on that...
This function returns a number corresponding to the button clicked in an alert message displayed by the Show Message script step. Returns 1 for the first button (by default, labeled OK), 2
for the second button (by default, labeled Cancel), and 3 for the third button.
Just change the labels in something you like, f.i. Searh again and Stop.
Make an if statement for both choices
If Status(CurrentMessageChoice)= 1
Goto layout (your layout)
Enter Browse mode
If.......= 2
Goto layout (your search layout)
Enter find mode
2. One record found
If (Status(CurrentFoundCount) = 1
Go to layout (the layout with the info you want)
3. More than 1 record found
If (Status(CurrentFoundCount) > 1
Go to layout (a layout showing the records in listview), from that point you can direct the user to the record of choice by providing a button that goes to the single record or a given layout from that record.
This is a rough approach but will give you a start to play with the function and scriptmaker.
Best is to write down in plain english what you want and look for functions that can do the job, rest is putting them together or moving around untill it works....
or not....
HTH
Start your script with 'Set Error Capture - On'
It will suppress the FM error messages (called error trapping) and stores the error number in memory temporarily.
Use this script step whenever you want to capture a FM generated error and perform your own sequence of steps.
After your perform find you can go different ways, depend on the result.
Add a few if statements, after your perform find scriptstep, to tell FM what to do when....
1. If no record found the error number is 401.
The scriptstep will be:
If (Status(CurrentError) = 401
Show Message ('No records were found')
Provide buttons to go to browse or search again, and use the
Status(CurrentMessageChoice) to act on that...
This function returns a number corresponding to the button clicked in an alert message displayed by the Show Message script step. Returns 1 for the first button (by default, labeled OK), 2
for the second button (by default, labeled Cancel), and 3 for the third button.
Just change the labels in something you like, f.i. Searh again and Stop.
Make an if statement for both choices
If Status(CurrentMessageChoice)= 1
Goto layout (your layout)
Enter Browse mode
If.......= 2
Goto layout (your search layout)
Enter find mode
2. One record found
If (Status(CurrentFoundCount) = 1
Go to layout (the layout with the info you want)
3. More than 1 record found
If (Status(CurrentFoundCount) > 1
Go to layout (a layout showing the records in listview), from that point you can direct the user to the record of choice by providing a button that goes to the single record or a given layout from that record.
This is a rough approach but will give you a start to play with the function and scriptmaker.
Best is to write down in plain english what you want and look for functions that can do the job, rest is putting them together or moving around untill it works....
or not....
HTH
- Status
Similar threads
- Locked
- Question
- Locked
- Question