Jump to content
Sign in to follow this  
Mark73

Display screen

Recommended Posts

Mark73    0

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 

 

Capture.JPG

Share this post


Link to post
Share on other sites
comment    1,369

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 by comment

Share this post


Link to post
Share on other sites
Mark73    0

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

 

Share this post


Link to post
Share on other sites
comment    1,369

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

Share this post


Link to post
Share on other sites
Mark73    0

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

 

Share this post


Link to post
Share on other sites
comment    1,369

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 ]

 

  • Like 1

Share this post


Link to post
Share on other sites
Mark73    0

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

 

Share this post


Link to post
Share on other sites
Mark73    0

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

 

Share this post


Link to post
Share on other sites
comment    1,369

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.

Share this post


Link to post
Share on other sites
Mark73    0

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

Thanks for your time I do appreciate it

 

Share this post


Link to post
Share on other sites
Mark73    0

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

 

 

Share this post


Link to post
Share on other sites
Mark73    0

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

 

Share this post


Link to post
Share on other sites
comment    1,369
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?

Share this post


Link to post
Share on other sites
Mark73    0

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 by Mark73

Share this post


Link to post
Share on other sites
comment    1,369

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.

Share this post


Link to post
Share on other sites
Mark73    0

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 by Mark73

Share this post


Link to post
Share on other sites
comment    1,369
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 by comment

Share this post


Link to post
Share on other sites
Mark73    0

Thanks Comment 

 

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

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.