I have Shipping table. It has one day for every ship date (ShipDate, date). It has LastCall (time) so shipper (or manager) can set the deadline to receive orders for any upcoming day (or that current day). Sales wants to know when this deadline is. But Shipper (or manager) can change the time. Currently, it auto-enters (without Replace) "10:00 AM". There is ShipmentsClosed (number) toggle so shipper can shut off taking orders also, even if before the deadline. ShipmentsClosed also triggers events plugin to run scripts.

If ship date/time has passed, it should list the next upcoming 'default' ship date/time. So I need to relate to Shipments on two different days (I think). If the shipment is within that same day, I want it to display in hours, as "Shipping deadline in 1 hour, 22 minutes." I realize even unstored it won't change immediately but that's okay.

If ship day is passed (in that same day), I want it to display as "Next Ship Deadline Tuesday, 10:00 AM." Or whatever the next record in Shipping says. The ShipmentsClosed should be at 0 (still open) or it ignores the current day/time (assumes deadline passed even if it really hasn't) and jumps to the next ship day/time. Oh brother.

Well, this is what they asked for. I told them I would try. I haven't a clue how to do this ... calculations, relationships or scripts. I've been chewing on this all day and have absolutely no idea even where to begin.

Can you point me in the right direction? Thank you!

Calculation, for sure.

The attached file should explain it better than i could in plain English.

HTH,

Jerry

bekah.zip

Nice job, Jerry. Might I suggest simplifying it a bit using Let,

ShippingTime :=

Let( T = Timestamp( Get(CurrentDate); Get(CurrentTime) );

Case( TodayShipTimeStamp > T; TodayShipTimeStamp - T; TomorrowShipTimeStamp - T ) )

and converting the Choose in DeadlineDisplay to

MiddleWords( "Sunday Monday Tuesday Wednesday Thursday Friday Saturday"; DayOfWeek(TodayShipTimeStamp); 1 )

or at least subtracting 1 from the test so that there isn't need for a "0" result?

Thanks, JT. Much more elegant, and good call with the Let function. My inexperience with v7 is showing...

No problem, Jerry. It takes a while to get the hang of it.

I just realized the first calc can be further simplified.

Let( T = Timestamp( Get(CurrentDate); Get(CurrentTime) );

Case( TodayShipTimeStamp > T; TodayShipTimeStamp; TomorrowShipTimeStamp ) - T )

By the way, Jerry, congratulations on passing the 700 mark and becoming an "Old Hand."

Oh! Thank you Jerry and JT for helping me!!

I won't be able to try this until tonight, but there's one thing I see; and maybe I just don't understand it all ...

When it looks to the next day's shipping date, it won't always be ShipDate + 1. I can't see how it will know what the next ship date is, unless it selfjoins to itself on, uh, I get stuck there - maybe a 'greater than' or something. Do you see what I mean?

The next shipdate might be three days in the future, and the LastCall may not be the default LastCall if the time has been shifted, so it'll need to look at that next ship date's LastCall also. Maybe your calcs take this into account but it doesn't appear to, so thought I'd ask. Thank you BOTH so much. I never would have used timestamp!

Transpower: Thanks. Nice to finally join the club.

Bekah: At first i had thought you posted this in the "Relationships" forum by mistake. Now i realize that it is just because you are more prescient than i am.

I would guess that you could use a < relationship ( Shipping::ShipDate < Shipping::ShipDate ) and sort the records in that relationship ascending to find the next ShipDate in the file; this would take care of the "ShipDate + 1" calculation you object to.

So say the self-relationship from ShipDate to ShipDate is a < relationship and is to the TO Shipping2; and that this relationship is sorted ascending by ShipDate. Then Shipping2::ShipDate would always represent the next shipping date. Thus, substituting (Shipping2::ShipDate) for (ShipDate + 1) in my original calc should adjust the calc the way you want it. Off the top of my head, i'm not sure how you would adjust -Queue-'s calc in the same way; JT?

Haven't tested any of this, but it sure looks good in theory. First one to test it posts the attachment?

J

Well, all I can say is that is seems logical. I don't have time to test it right now. My Accounting Supervisor just overhauled the form I've spent three weeks creating. So I'm basically starting from scratch and will be absent for a while, maiming and tweaking my baby and expanding my javascript and css knowledge.

Thank you both for helping! I won't be able to try this until tomorrow but it makes sense. In fact, if I join on 'less than' to Shipping2, and they naturally are created in ascending date order, then it should always relate to the first next date (because it'll be the first record in the relationship)!

In fact if I understand it, a Lookup would also work here except I'd be concerned that, if someone changes the LastCall in Shipping2, it wouldn't change in Shipping1. Maybe I should keep it as unstored calc just to be sure. But I'll play with both your ideas because I want to understand them both. And then maybe I'll come up with a demo!!