HI all

First post here so please be gentle,

I have a database for all our installation and i would like to put a display screen up that shows the install booked for each product we sell for the current week and next week

I have written the script to do the correct search and this is working fine   

but Im having trouble getting this to display correctly in my install display layout, i have created a sub-summary to sort by product but need help with splitting the install for each week

I have attached what i would like the layout to look like 

I hope this make sense

 

many thanks

Mark 

 

This would be very easy to produce, if you were willing to accept an entirely vertical arrangement, i.e.

This Week:
 • Widget ...  4
 • Gadget ...  5
 Total ......  9
Next Week:
 • Widget ...  3
 • Gadget ...  2
 • Doodad ...  5
 Total ...... 10

 

 

Edited April 11, 20169 yr by comment

Thank you for responding a vertical arrangement would work perfectly fine im sure

 

Well, then you only need to find the installs planned for this and the next week, and summarize them by week and by product.

I think i might have over complicated it, I have created 2 fields calculation

Current week 

If(
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 2 )) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 3 )) or
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 4 )) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 5 )) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 6 )) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 7 )) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 8 ));1;0)

Next week

If(
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 9)) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 10)) or
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 11)) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 12)) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 13)) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 14)) or 
(Start Date= ( Get ( CurrentDate ) - DayOfWeek ( Get ( CurrentDate ) ) + 15));1;0)

but for some reason its not displaying the install found for the 2 week perieod

thanks

 

You only need one calculation field (let's call it cWeek) =

Start Date - DayOfWeek ( Start Date - 1 ) + 1

Set the result type to Date and make sure the calculation is stored. This calculates the starting Monday of the week in which Start Date is (I am guessing from your calculation that your weeks start on Monday).

To find records in the current and the next week, have your script do:

Enter Find Mode[]
Go to Layout [ "Summary" (Installations)]
Set Field [ Installations::cWeek ; Get(CurrentDate) - DayOfWeek ( Get(CurrentDate) - 1 ) + 1 ]
New Record/Request[]
Set Field [ Installations::cWeek ; Get(CurrentDate) - DayOfWeek ( Get(CurrentDate) - 1 ) + 8 ]
Perform Find[]
# Sort by cWeek and by the product field
Sort Records [ Restore ; No Dialog ]

 

Thanks again for you fast response 

I have not had time to try this but will let you know How i get on

 

Many thanks

 

Finally had time to get back on this little project

Thanks to the help about im now able to search for 2 weeks of install not just need help with the best way to show the results on a display screen in the office

I need to show how many of each product is booked for install each week and the total installs each week

 

Many thanks

 

As I said back in April, this is easy to do with a vertical arrangement.  Sort the found records by cWeek and by product and show them using a list layout with two corresponding sub-summary parts and no body part. Use a summary part defined as Count of [any non-empty field] to provide the counts.

Really sorry Comment I just not getting it could you possibly show me an example 

Thanks for your time I do appreciate it

 

Managed to get it to work, for some reason just woke this morning and understood what you meant 

so simple don't know why I want getting it

thank you for your help comment

 

 

Now the install screen up and running great thanks Comment I need to work on the sales side of things this will pretty much the same sort of thing but i need to find last weeks and this weeks sales

can you help me with the script to grab last weeks data

 

Many thanks

 

3 hours ago, Mark73 said:

need to work on the sales side of things this will pretty much the same sort of thing but i need to find last weeks and this weeks sales

Do you only need to find them, or do you also need to group them by week, like you did with the installations?

need to do the same as we did for installation 

I was thinking of showing the total of sale last week ie:20 and then break them down by status ,new job, booked, survey booked etc) and then show the sales this week along with the status of those sales and also the product but not sure if that doable

hope that make sense 

 

Edited January 16, 20178 yr by Mark73

If you want to group them by week, you will need to add a cWeek calculation field in that table, too. Then the process of finding becomes the same as outlined above.

As for the additional sub-grouping, it's only a matter of adding more sub-summary parts and sorting the records accordingly.

I tired that but couldnt workout how to find the previous week 

 

 calculation field (let's call it cWeeksales) =

contract date - DayOfWeek ( contract date - 1 ) + 1

Then create the script like you advised about, but what do i put to find previous week

Set Field [ Installations::cWeeksales ; Get(CurrentDate) - DayOfWeek ( Get(CurrentDate) - 1 ) + 1 ]

This find current week but not sure how to change this to find pervious week

 

thanks

 

 

Edited January 16, 20178 yr by Mark73

14 minutes ago, Mark73 said:

what do i put to find previous week

 

Set Field [ Installations::cWeek ; Get(CurrentDate) - DayOfWeek ( Get(CurrentDate) - 1 ) - 6 ]

Edited January 16, 20178 yr by comment

Thanks Comment 

 

I had put - 8 and not - 6, I really struggle with dates on filemaker