Key Calcs Filter Incorrect Dates

[LaRetta]

I have three portals displayed on a layout which filter upcoming shipments (Invoices) according to a combination of flags, status

[BobWeaver]

Just a guess, but here are things to check:

1. The calculations are unstored;

2. Results of the calculations are set to date type;

3. Try enclosing the results of your calculations in GetAsDate() functions.

[LaRetta]

Thank you Bob!

1) and 2) have been verified. I'm unsure why I might need to use GetAsDate() since the calculations are date and produce date results properly and join to dates (ScheduledShipDate). There are no multi-keys only date.

The calculations just give the wrong date results. I just can't seem to get them to work in conjunction to produce the Next and then Next (Future) dates - skipping weekends.

[BobWeaver]

Oops, I misread your earlier post. Try these calcs:

Next:

Get(CurrentDate) +

Choose(

DayOfWeek(Get(CurrentDate) -1); 1; 0; 0; 0; 0; 3; 2)

Future:

Get(CurrentDate) +

Choose(

DayOfWeek(Get(CurrentDate) -1); 2; 0; 0; 0; 4; 4; 3)

[comment]

I am not altogether sure what is it that you're asking. In general:

Shift date forward in order to skip weekends (Saturdays and Sundays):

date + Choose ( Mod ( date + 1 ; 7 ) ; 2 ; 1 )

To skip Mondays as well:

date + Choose ( Mod ( date + 1 ; 7 ) ; 3 ; 2 ; 1 )

[LaRetta]

Bob's response using DayOfWeek() makes perfect sense - thank you Bob!

I've been chewing on this Mod() calc for awhile now. It works perfectly too, of course! And I see my error on not projecting ahead to the last day of the week. But Mod() is a bit of a struggle to grasp, Michael. Can you check my thinking on why it works?

FM dates are numbers (732088) and this numbering somehow started a long time ago (smile) with date 1 which was Sunday DayOfWeek 1. So using Mod(date; 7) will produce 0 if Saturday, ie, number-date (732088) divided by 7 with no remainder and Choose() thus returns the first Choose() position for a 0 result - or 2 on the first calc.

The divisor must always be 7 or this won't work with dates to tell me the DayOfWeek. But Mod(date; 7) number will always tell me the DayOfWeek. This is powerful stuff if I can thoroughly understand it. Am I starting to *get* it?

LaRetta

[comment]

The principle is correct, some of the details less so.

Filemaker's first date is Jan 1, 0001. This is actually Monday.

DayOfWeek() is nothing but:

Mod ( date ; 7 ) + 1

For the 'skip weekends' formula, we need 0 to fall on Saturday and 1 on Sunday. In the first case you need to add 2 days, in the second case 1 day, and nothing in all other cases. That will fit Choose() perfectly.

You could use:

DayOfWeek ( date + 1 ) - 1

Which is exactly the same as:

Mod ( date + 1 ; 7 )

[LaRetta]

Bingo!! When I pull a concept such as this into my Principles db (and GET it), it's like I've been handed a million dollars!

[LaRetta]

I've attached a demo of what I have. Today my portals display correctly. If I change my system date to 5/23, an Invoice dated 5/24 doesn't appear in the Ship Next portal - it jumps to Ship Future. Ship Next calc displays 5/25 instead of 5/24. It certainly isn't time to feel smug about any understandings I thought I had about date calcs. Why are three simple portals proving more difficult than inventory or other more complex issues? Is this an indication that my entire thinking process on this piece sucks? I'll consider changing it if other ideas are presented.

And there is something else I didn't plan for. Orders were taken on Friday AFTER the ship process was locked (usually around 1PM). And orders were released to ship (either back orders came in or they were approved late on Friday). This means they should ship the following Monday instead, but they still appear in the Ship Today portal. I should have considered this possibility.

So I added a Closed (date) field signifying that any Invoices which would normally relate on Get(CurrentDate) >= ScheduledShipDate should no longer appear in Ship Today if the current date in Closed (that day or a weekend) but should move to Ship Next.

I'm hoping a demo shows clearly what I'm dealing with here. I could waste the rest of my weekend tweaking this puppy but I think I need a new clear perspective before I continue. Shippers want to view shipments together not have to add two portals (and summaries) together.

BTW, the reason I have several flags (and I didn't include them all) is because I have BackOrder flags, Review Flags and such and various portals display for various staff (up to 5 portals on a layout). Originally I had them combined as Keys (example 0 1 0) but found that the ability to mix and match them allowed more flexibility in establishing various relationships to meet our needs. Make sense? I hope so!

LaRetta

PS - I had a Ship Flag backwards and corrected it. ShipStatus should be 1 and relationship based upon not equal. It doesn't affect the problems I'm dealing with but it might be confusing as it stood. Sorry 'bout that.

Orders.zip

[BobWeaver]

I didn't try to follow Comment's formula, but I see that there are two errors in mine. One of the closing parentheses is in the wrong place, and the zeroes in the choose function should be changed to 1 or 2. So, my corrected formulae are:

ShipNext =

Get(CurrentDate) +

Choose(

DayOfWeek(Get(CurrentDate)) -1; 1; 1; 1; 1; 1; 3; 2)

ShipFuture =

Get(CurrentDate) +

Choose(

DayOfWeek(Get(CurrentDate) )-1; 2; 2; 2; 2; 4; 4; 3)

That's what I get for posting before testing

[LaRetta]

Thanks Bob

I've modified my file using your calculation (new file posted). I'm playing with Mod() for this - it'll help the concept sink in about Mod() but I'm too darned tired to think clearly and can't get the calcs right. I put a global 'system date' on the demo to make changing real-time dates easier for testing.

I've modified the Ship Today portal so that 1) If shippers enter a date in Closed or it's a Saturday or Sunday, no additional shipments appear in the portal! This means shipping is closed. They can click the Closed date field to again 'open' it to receive a few additional shipments if they wish.

I've added a Ship In Process portal (I had it in my design now and it's working) but I wanted everyone to see the movement of the orders through these portals (click Ship These Orders). Once an order moves into Process, it appears in in this portal. This portal (and it's summ'd data) is then used to determine packing materials, labels and even UPS and truck requirements. They print summ'd product counts which are taken into warehouse to pull stock. This portal also has an 'unship' button (not in demo) in case a shipment must be manually pulled from the system.

Now Ship Manager can view daily shipments 'unshipped' only - and decide whether to process more through the system. But once he Closes, all remaining unshipped should NOW start accumulating in the Next portal.

I am unsure how to adjust the Next portal to allow for this, ie, when shipping closed all orders unshipped (even back prior dates) should then appear in Next. The reason this is important, is another Shipper schedules the next day's requirements according to the Next portal. It needs to include those from today that didn't ship.

I think I need the Ship Next join to reflect <= . I used a date to signify Closed, thinking I might need it to restrict this relationship.

You both have been very helpful. Any last ideas would be appreciated. Various staff sees (and uses GTRR) various portals at one time. Ship Manager can view orders attempting to ship Today (and decide whether to take any late ones) and view next day's shipments to plan tomorrow's stock pulls as early as mid-afternoon the night before. They don't want to add counts and product quanties from two portals to project the next day. Once shipments for the day are closed, they want them all in the next portal.

LaRetta

OrdersModified.zip

[LaRetta]

I'm going to try one last time out of desperation (and I'm attaching my test file) ...

The process was working except it was displaying on weekends. Bob and Michael helped me with that. But when I added the Close Shipments date, it all breaks. Ship Today should show shipments with ScheduledShipDate today or prior (that haven't started through the ship process). Once Closed Shipments is clicked, all shipments yet unshipped should instead appear in Ship Next (so no more shipments can be processed that day).

I should be able to get this to work whether using Bob's principles or Michael's and I've been working with both of their calculations.

Today, I learned to change it to <> AND (on another post) for the Ship Today. But my problem is more than that. I've been studying dates, Mod(), Choose(), DayOfWeek() and date range relationships for 5 days and can't solve it. I will get one portal working, only to have the next portal break (overlap display of dates or skip dates) or jump forward incorrectly. I have so much I've tried and that I've been learning (cramming) that I'm totally perplexed on what to use.

I hope someone sympathizes and wades through this thread and helps me solve it. I don't want to re-write the whole shipping process (which works a treat for us) just because my displays break when I try to add a cut-off date. And I've started just plugging in anything to see what happens ... dart-board design is not a good idea. It has beaten me.

LaRetta

OrdersModified.zip

[comment]

I've seen your first file, but there seemed to be too much going at once (development-wise) for me to get a grip on things.

Trying to look at it from the POV of the shipping manager, I think I would like to see the following portals:

LEFT SIDE:

1a. Backlogged Shipments (ScheduledShipDate < gToday AND not <something>);

1b. ScheduledToday Shipments (ScheduledShipDate = gToday AND not <something>);

2. ScheduledNext Shipments (ScheduledShipDate = cNextDay AND not <something>);

3. ScheduledNextNext Shipments (ScheduledShipDate = cNextNextDay AND not <something>);

RIGHT SIDE:

4. ShippingToday (PhysicalShipDay = gToday);

OPTIONALLY:

5. ShippingNext (PhysicalShipDay = cNextDay);

6. ShippingNextNext (PhysicalShipDay = cNextNextDay);

<something> would be a flag indicating that the item has been selected for physical shipping, e.g. not IsEmpty (PhysicalShipDay).

Now I would like to be able to click on a portal row to shuffle an item from Sheduled/Backlogged to Shipping (and back), so that I can organize my work visually. (Clicking on an item puts gToday into PhysicalShipDay, or clears it).

If I have the two additional portals, then perhaps I need to be asked, when I click a scheduled item, where to put it (ShipToday/ShipgNext/ShipNextNext).

gToday is a global date - I can shift it to any (working) day. When I say no more shipping today, I simply move it to the next day, and that's it.

cNextDay = gToday + 1 + Choose ( Mod ( gToday + 2 ; 7 ) ; 2 ; 1 )

cNextNextDay = gToday + 2 + Choose ( Mod ( gToday + 3 ; 7 ) ; 2 ; 1 )

If there's no shipping on Monday, then adjust accordingly. Otherwise I don't get the reason for handling Monday at all.

BTW: for good order, no one should be allowed to schedule shipping on a weekend - auto-enter calc in ScheduledShipDate should take care of that.

Is this simple enough? So why won't it work?

[LaRetta]

Oh Michael. Thank you. Please hang in there with me. I've pruned this repeatedly and it's still very long. As you say, much is going on and that

[comment]

I had only time to skim your reply, so I hope this is correct.

The close day flag does not seem to me to be a good idea - at least not when it influences what goes into which portal. Look, this is basically a calendar. One should be able to flip thru days and see what's scheduled for any day, or, in this case, for any 3 days.

You don't declare a DAY closed in a calendar. You declare TASKS as finished or not.

We have two directions here: up/down and left/right. The up/down direction is this: when I switch gDate to next day, items that were scheduled to ship today should stay in the topmost portal, and items that were scheduled to ship tommorow should jump up there and join them, and so on.

The left/right direction is the approval or whatever flag you have to indicate that the item is not only scheduled to ship, but something is actually being done to make it happen. When that flag is turned on, the item needs to fly across to the right-hand side portal/s. As far as scheduling is concerned, this task is finished. That is that <something> I am talking about.

I suggest you get this working in stages: get the up/down working first, by dates alone. If you want only 3 portals, that would be:

ScheduledShipDate <= gToday

ScheduledShipDate = cNextDay

ScheduledShipDate = cNextNextDay

Once you got that working (and why wouldn't it), add the AND <something> condition and work on that. Divide and conquer.

HTH

[LaRetta]

Thank you, Michael!! I am moving forward on this right now!

One should be able to flip thru days and see what's scheduled for any day

[comment]

My bank used to have a practice of closing the business day early in the afternoon, 3PM I believe it was. When they opened at 4PM, they would stamp everything with tommorow's date.

[LaRetta]

Mid-day bank cutoff! Yes! That's what I needed!

We can't set a firm time cut-off but that was why I created a CloseDate instead of simple close flag so I could use it, ie, override the gCurrentDate (which is only a field right now for testing. This will be Get(CurrentDate when active). Purpose of Close Date w/ system date was to kick the day one forward.

Nimrod LaRetta said

OrdersFinalMaybe.zip

[comment]

Can't do now - perhaps later.

So why not do as the bank does - declare it's tommorrow (set gToday to cNextDay) and that's it? It seems to me most of your complications stem from here.

[comment]

I see something like this (attached).

Note that the NextDay relationship is designed to catch the unlikely case of a shipment scheduled for weekend. If you're confident about that, you can simplify it to:

cNextDay = ScheduledShipDate

AND

constant0 = cIsShipping

sOrders.fp7.zip

[LaRetta]

Hi Michael

Well, we're close. But the portals still aren't shifting properly. For the test, I added a few orders scheduled prior to June and I removed the weekend entries (field validation on ScheduledShipDate won't allow weekend dates).

The Next portal should only show orders scheduled for one day. I added 5/25, 5/30 and 5/31 and changed 6/4 and 6/5 to 6/6. gToday shows Friday, June 3 and the results are correct.

But if I change the date to 5/30, the Next portal shows 5/31 through 6/3. It should only show 5/31 and Look Ahead should continue 6/1 forward. I then changed the Next relationship as you suggested. NextDay to = ScheduledShipDate and removed the gToday < ScheduledShipDate.

5/31 disappears from all portals when I enter 5/30 as gToday. I placed Shipping fields on table and gToday shows 5/30 and NextDay shows 6/3. When I change gToday to 5/31, NextDay jumps to 6/5. Thinking it might be portal refresh problem, I closed and reopened. Nada. If it's working right for you, could it them be a date conversion issue? I can attach my working copy of your demo if you wish but that's probably unnecessary.

Arrows: When gToday is 5/30 and I click the forward arrow, gToday jumps to Sunday, June 5.

We're very close! Your demo rocks! I hope you can help me figure this out.

LaRetta

[comment]

I cannot reproduce the problem. I don't quite see how this could be a date format issue, but instead of speculating, why don't you clone the file and see if it helps?

[LaRetta]

You were so fast! Well I realized I snapped a picture of the wrong file so I deleted them ALL, downloaded your file and re-created the test. The first shot - one attached here is with no changes to the relationship or calcs or anything.

It's right except it includes more records in Next than just one day. Had trouble getting a gif - we windows users aren't as spoiled with graphic abilities like you are.

[comment]

Nope. I suggest the .gif format is best for screenshots. See mine.

[comment]

FWIW, I have downloaded my own attachment - still works OK.

[LaRetta]

First snap is attached above - I changed my deleted post because I couldn't attach two pics to one post.

Okay, here's the one where I try to restrict the second portal to only show the next day.

All I changed is relationship NextDay = ScheduledShip and removed the gToday line.

So your demo works except more than one day appearing in Next (in first pic) and disappearing in second pic. And if I attempt to restrict Next to one day, it breaks because cNextDay shows 6/3. The NextDay calc jumps ahead more than one day (see field on top of pics to left of gToday) - even when gToday is a weekday such as Monday.

[comment]

I don't know - something is very wrong here. The only thing I can suggest is download my file again, clone it and before making any changes (other than changing the scheduled ship dates in the Invoices table) see if it misbehaves.

[LaRetta]

I did that three times and the first gif (attached above) is the result - after clone with no records and NO changes to it. I just deleted everything and tried again. cNextDay displays 6/3 when gToday is 5/30.

I will try re-creating your demo from scratch.

[LaRetta]

Well, I'm posting back. I doubt anyone but Michael has hung in there. But just in case, I'd like to explain my findings:

I've attached another screen shot. I re-created Michael's calc in a brand new file (displayed left side define fields then resultant brand new layout FM created). As you will see, it displays 6/3 when 5/30 is entered. Wrong.

THEN ... I changed the demo file. I created a new global called gNewToday (global date). And I repointed NextDay calc and all relationships to it instead. In the view to the right is that same file I posted earlier. I respecified gToday May 30, 2005 field to new global gToday. Please look to the left and see the NextDay calc.

I just created this same calc in vs. 6. It displays 5/31 as it should have. It must be my system. For the life of me, it shouldn't matter but I'm not insane. I was beginning to wonder. I'm using XP Home Edition.

Hey! This might actually be good news! Maybe this is why sometimes my date calculations produce results I think they shouldn't!!! Could this be it and not just that I don't understand the date logic? No wonder these calcs weren't making sense ... I understood their logic but when working with them, they never produced logical results and so I doubt myself and my thinking constantly!!

Okay, okay ... I can't use that for ALL my inabilities to solve many calculations ... but it certainly explans this one! I understood NextDay calc but, when it produced 6/3, I assumed I misunderstood everything I thought I was learning!! I just don't assume computers can make mistakes. But they can. And my screen shots show it.

WHEW! My problem isn't solved but my sanity is still intact (pretty-much)!

LaRetta

[LaRetta]

Okay, thanks to Michael, we broke the calc down into two calcs and created a third for the joins. It works!!!

gToday = 6/3/2005

calc 1: gToday + 1

result: 6/4/2005

calc 2: Choose ( Mod ( gToday + 2 ; 7 ) ; 2 ; 1 )

result: 2

calc 3:

calc 1 + cal 2

result: 6/6/2005

But a combined calc of:

gToday + 1 + Choose ( Mod ( gToday + 2 ; 7 ) ; 2 ; 1 )

result: 6/8/2005 (WRONG)

Just in case anyone is interested. It is probably an isolated problem with XP Home Edition with 7.0v1. But at least I can finish this project tonight and I'm moving upgrading to 7.0v3 to the top of my tight priority list!

Thank you Michael for hanging in there!

[comment]

That is great. Why don't you put your computer in the doghouse and plug in the dog instead? You might get better results...

[LaRetta]

My computer will become a doorstop. My graph won't even color (although this design shows colored at work).

I'm buying a Mac so I can be sure my designs are truly xplat. Besides, I want the cute little pen you Mac people get so you can point-select colors. I'm an artist not a mathematician.

[Søren Dyhr]

Besides, I want the cute little pen you Mac people get so you can point-select colors. I'm an artist not a mathematician.

You have something similar...

http://www.nattyware.com/pixie.html

...a tip i found just yesterday in this COOL new release: http://previews.filemakermagazine.com/videos/579/BetterInterface.mov

--sd

[LaRetta]

Oh my, Soren!

Such fun! And it lifts my spirits after spending a week thinking I couldn't do ANY math right (the Choose() date relationships on this thread).

Much appreciated.

LaRetta (off playing with colors and layouts in Oregon)

[Søren Dyhr]

LaRetta (off playing with colors and layouts in Oregon)

It's less harmfull than roasting your pet by pluggin' it in...

--sd