Using an "and" parameter or a date calculation in a calculation

[mpau]

Hi All,

I'm trying to create a calculation that calculates matching records based on a date criteria and have been unable to do it. I successfully use the following calculation below to track current inventory levels:

Field is "OnOrder"::Case(LineItem::_fkProductID = ProductStyles::_pkProduct;Last (LineItem::OpenTotal) * PartQtyNeeded;0)

What I'd like to do is create a calculation that sees the inventory levels for one week and then two weeks prior. I thought I could simply add a second criteria to the calculation above to get the result. So for the week prior the calculation would look like:

"OnOrderWeekPrior"::Case(LineItem::_fkProductID = ProductStyles::_pkProduct and LineItem::SaleDate = WeekOfYear(Get(CurrentDate)) - 1;Last (LineItem::OpenTotal) * PartQtyNeeded;0)

But when I add a second match criteria to the calculation it fails, and returns a "0" value. When I run the calculation as follows it also fails,

"OnOrderWeekPrior"::Case(LineItem::SaleDate = WeekOfYear(Get(CurrentDate)) - 1;Last (LineItem::OpenTotal) * PartQtyNeeded;0)

So I'm presuming that the issue has to do with calculating based on a calculated date parameter.

Any thoughts out there?

thanks!

[Fitch]

One way to achieve this would be to create a week field in the line item table (auto-enter the week of the order date), and create a "week prior" calculated field in your product table (or whatever the context of these calculation is -- you didn't say). This calc should be unstored. Create a second relationship to line items that includes these two fields. Does that make sense?

[mpau]

Makes perfect sense. Unfortunately, doesn't seem to work. I'm unclear as to why I'm able to properly calculate based on productID match criteria but not based on date criteria. Oddly, I am able to get the proper date related Lineitem::OpenTotal value via filtered portals but can't pull the portal field data from the portal to use in a calculation.

The calculation is created within the context of the Inventory Log which is a join table between Products and Parts that keeps a running log of the totals of each and their respective draws on one another. The Lineitem table is a join table between Orders and Products.

[Fitch]

So your relationships look like:

Orders - Line Item - Products - Inventory Log - Parts

Is this correct?

Part of the problem may be that your relationship between Inventory Log and Line Item is going through Products, so you're not getting the result you expect (you are getting the logical result, though).

If the context of your calculation is Inventory Log, try making another table occurrence of Line Item and directly relate it to Inventory Log. Base the calculation on that.

[mpau]

The relationships is actually Orders - Lineitem - MasterProducts (base products)- Products (product styles/options) - Inventory Log - Parts

Like you, I thought that the problem might be that Inventory Log was going through too many tables to properly calculate, so I created another table occurrence of Lineitem and directly related it to Inventory log but the results didn't change. It seems to be to have something to do with using a calculated date field as part of the calculation. Does that make sense to you? Unfortunately, as the Inventory Log doesn't create date based records, I don't know how to change this. The inventory log creates static records that are based on relationships between parts and products and they hold running and constantly changing tallies based on the draw from both sides. It works great as far as tracking inventory goes, but the failure is that I can't create any kind of report except for the current date. Maybe my entire structure is wrong. What do you think?

[Fitch]

Since you were able to get the result you wanted via a filtered portal, maybe the answer you need is in Kevin Frank's series on filtered portals, or this approach by Goya, or Soliant's blog.

[mpau]

Thanks I'll check those out. Since our last exchange I had an odd and similar issue crop up in my database. For two years now I've successfully used a set up to create shipments that relies on multiple table occurrences of my Lineitem table, one for shipped items (Lineitem_ShipmentsSHIPPED) and one for unshipped (Lineitem_ShipmentsTOSHIP), each having a unique relationship to the Shipments table.

For Shipped Items the relationship is as follows: Lineitem_ShipmentsSHIPPED::_fkShipmentID = Shipment::__pkShipmentID.

For Unshipped Items the relationship is: Lineitem_ShipmentsTOSHIP::_fkOrderID = Shipments::_fkOrderID AND Lineitem_ShipmentsTOSHIP::FlagShipped ≠ Shipments::ConstantOne

Key fields above -

LineItem::Flagshipped (Calculation) = Case ( not IsEmpty (_fkShipmentID) ; 1; "")

Shipments::ConstantOne (Calculation) = 1

On shipment layouts I have two portals one for Lineitem_ShipmentsTOSHIP and the other for Lineitem_ShipmentsSHIPPED. There are script trigger buttons in each that move the items from one window to the next by setting the values of either Flagshipped or _fkShipmentID. It's always worked beautifully. Until now. Oddly, the Lineitem_ShipmentsSHIPPED still works but the Lineitem_ShipmentsTOSHIP no longer shows anything. I haven't changed anything. I spent about an hour yesterday testing and checking the calculations to make sure everything was fine and couldn't find anything wrong. In the end, the failure seems linked to the AND criteria in the table relationships. By changing the Lineitem_ShipmentsTOSHIP portal to a filtered portal of the Lineitem table it worked. (filtering by the same calculation as the table occurrence (so Lineitem::_fkOrderID = Shipments::_fkOrderID AND Lineitem_::FlagShipped ≠ Shipments::ConstantOne)

I'm beginning to wonder if there's a bug with the AND criteria in table occurrences. Has anyone had a similar experience? It would explain both of the issues I've been having. But the question is, why now? I can't remember doing any updates.

[Fitch]

It seems to me I read somewhere about a behavior change in FileMaker 11 regarding empty fields in relationships, but I can't find the reference at the moment. Based on this vague recollection, I'm suggesting that it may not be the AND that's the culprit, but rather the Flagshipped calc.

Might be worth a try to change this:

Case ( not IsEmpty (_fkShipmentID) ; 1; "")

to this:

not IsEmpty(_fkShipmentID)

That will give you a 1 or 0 rather than 1 or blank.

You could also see if using "< ConstantOne" in the relationship rather than "≠ ConstantOne" does any good.