1 of 1 people found this helpful
I didn't test this, just typed it out in Notepad ...
DaysToAdd = Case (
DayOfWeek ( Onsite_deadline ) = 1; 1; // Sunday
DayOfWeek ( Onsite_deadline ) = 7; 2; // Saturday
0 // any other day
Month ( Onsite_deadline );
Day ( Onsite_deadline ) + DaysToAdd;
Year ( Onsite_deadline )
GetAsTime ( Onsite_deadline )
Thank you so much. This worked straight off the bat.... genius. So grateful you replied.
Dave, you can call me Dave.
just to optimize a bit:
deadline = TO::Onsite_deadline;
DaysToAdd = Case (
DayOfWeek ( deadline ) = 1; 1; // Sunday
DayOfWeek ( deadline ) = 7; 2; // Saturday
0 // any other day
Month ( deadline );
Day ( deadline ) + DaysToAdd;
Year ( deadline )
GetAsTime ( deadline )
Before I reach out to the wider community I thought I might ask you directly seeing as you were amazing in solving my last sticking point in my system. I really hope you don't mind... I am actually building a customer system to dovetail our service operations into HP, and we are one of Europe's largest contract providers for HP - so they are watching our progress with a whole heap of interest as what we have delivered so far has ticked all the boxes of what they would like to achieve for themselves having just bought the Samsung Printing Business. I am ex HP (7 years) and was the UK Marketing Director of Canon and MD of Samsung IT in the UK. I am the hands on CEO of a specialist IT service provider for HP and we do their Government contract work - tenders, deployments, pricing, support, etc. I liaise with HP Palo Alto, Boise and Barcelona for our higher level contact work.
I was cheekily hoping you might be able to guide me a little in this bit. Can't promise anything in return although I have talked a lot about you in the right circles. Here is what I was going to post to the community.
I am hoping someone might be able to help me with what may be a fairly simple conditional look-up calculation. I am building a billing system that uses start and finish meter readings for assets.
The assets all have a unique identifier which is its serial number and this goes into the field “Serial Number”. Each month I run a report that captures start readings at the start of the month and end readings at the end of the month – or the nearest approximate dates when the readings have been taken. The actual start date is a timestamp field that goes into the field “First meter Date” and the end meter date goes into the field “Last Meter Date”.
The values are “Mono Pages Start Meter”, “Colour Pages Start Meter”, “Total Pages Start Meter”, “Mono Pages End Meter”, “Colour Pages End Meter”, “Total Pages End Meter”. These values are imported into a table for all assets and a record contains all the metered values at the start and end, plus the serial number of the asset being tracked. The records are pre sorted so that they run by serial number and then descending “last meter date”. The record is given an auto serial number ID in increments of 1 which is called “__pk_meter ID”. In other words for argument’s sake the Jan values for asset “x” have an ID 1, then Feb an ID of 2, etc as I imported all them in one process. There are currently 5,000 assets with meter records for each month for the last few years.
I want to be able to cross reference the end meter values of the previous month’s meters (“last meter date”) and use the values in these fields for billing purposes against that serial number in the current month (ie the maths is
“Mono Pages End Meter” //of this month
- “mono pages end meter”//of previous month
Unless I do this, dependent on the time of day or even the date that the first reading for that month is taken, the billings will miss the lost values between the next month’s start and the previous month’s end if I use the start readings in the calculation – this could be an overnight value or even several days sometimes. Sometimes the system doesn’t take a reading for several weeks and can miss a month or so – so I need to look for the last value recorded in terms of date as it could be more than a month back.
How would I cross reference back saying something like assuming we are say in October 2016:
Take existing record Asset Value = “serial number”, use it to search all other records with that value, find the record where the “last meter value” is one month back or perhaps more but is the closest date to the current record value, then take all the values corresponding to that date to put them into a new fields in the current record. So I would call the new fields “Mono Bill End Meter”, “Colour Bill End Meter”, “Total Bill End Meter”. I am not sure if a script could perform this on request when I click a button that says “Generate Billing Info”.
I can then perform a simple calculation that says new bill = (“Mono Pages End Last Meter” – “ Mono Bill End Meter”)* unit value.
Any ideas very gratefully received.
I’m working out of doors today and won’t be able to look at it until later.