Need help with calculation field

[djeans]

I have a problem trying to use a calculation to pull information from a related table that hopefully someone here can point me in the right direction to solving.

I have two tables, related by a barcode field. On the first table (PendingOrders) I have a list of items that I have ordered, how many were purchased, and a projected arrival date. Any given item (same barcode #) may appear on the table multiple times with different projected arrival dates

On a second table, I'm trying to add calculation fields that will tell me how much of each item I have projected to arrive this week, how much next week, and how much is expected to arrive in 3 weeks (as well as many other fields, but those aren't important  for this question, I don't think).

The problem I run into, is that only the First projected arrival date returns a useful number. For instance, if I have widget A expected to arrive all 3 weeks, the first calculation field returns a result, but the other two do not.

Here are the calculations I'm using.

For orders arriving this week

Case ( PendingOrders::ProjectedArrival  <  Get ( CurrentDate ) + 7 ; PendingOrders::s_purchase ; "" )

For Orders arriving next week (week2)

Case ( PendingOrders::ProjectedArrival  <  Get ( CurrentDate ) + 14 and PendingOrders::ProjectedArrival   >   Get ( CurrentDate ) + 7 ; PendingOrders::s_purchase ; "" )

and for the 3rd week:

Case ( PendingOrders::ProjectedArrival  <  Get ( CurrentDate ) + 21 and PendingOrders::ProjectedArrival   >   Get ( CurrentDate ) + 14 ; PendingOrders::s_purchase ; "" )

Can anyone help me trouble shoot this.

Edited September 8, 2019 by djeans

[comment]

A calculation referencing a field in a related table will always use the value from the first related record. 

IMHO, a better approach would use 3 one-row portals, each filtered to show only records from a specific week and containing a summary field counting the records (defined in the child table). This is assuming you only want to display the values, not use them in further calculations.

[djeans]

Thank you for the reply.

I actually am going to have to use them in further calculations.

It's part of an inventory/order projection program I'm trying to make.

I have a layout, that when in preview mode, shows a list of products and how much of each I have on hand, calculates my average usage per week, takes into account what I have on-hand, and then recommends how much to order for next week. That all works fine. For a few of the items that I have to have shipped to me (that takes 3 weeks to arrive) I'm trying to have it also keep track of what's on-order and arriving each week, and also be able to project what I need to order this week, in order to have the right amount on hand 3 weeks from now. Kinda complicated to explain, but this screen capture of the order layout may help to make sense of it.

 

 

[comment]

5 minutes ago, djeans said:

I actually am going to have to use them in further calculations.

That's going to take some work. Is there a quantity field in the child table that needs to be totaled, or is it enough just to count the records in the corresponding week?

 

[djeans]

Yes, there is a quantity of purchases that would need to be totaled for each. 

[comment]

OK, but now I am somewhat confused by your c_WeekNum field. In your opening post, "this week" seems to mean the next 7 days, no matter what day of week today is. Week numbers, OTOH, tend to consider "week" as Sunday to Saturday (or Monday to Sunday).  Which method do you want to use for this?

 

Edited September 9, 2019 by comment

[djeans]

This report would typically only be ran on Monday, with my orders for "this week'' coming in that following Thursday. The weeknum column is the number of the week that the given item is projected to arrive (Monday to Sunday)

I guess the "this week" calculation is always going to reference the next coming Thursday in my scenario.

[comment]

6 hours ago, djeans said:

I guess the "this week" calculation is always going to reference the next coming Thursday in my scenario.

Not quite sure what to make of that.

See if the attached file can help. It uses a repeating calculation field to copy the quantity into one (or none) of three columns, according to the week of the date. In this calculation, a week runs from Sunday to Monday. A repeating summary field is used to total the columns. If you need to use these totals in further calculations, you can use:

Child::sTotalQtyByWeekR[2]

to get the total of the 2nd week, and so on.

 

SplitQty2Weks.fmp12

[djeans]

Sorry if it was a little confusing. The c_weeknum field was added just so I would know what fiscal week product was arriving for, but I had been basing all my calculations on the projected arrival date (which is supposed to always land on a Thursday). 

 

Thank you you for the file. I will try it out when I get home later tonight. 

Thank you so much for the help.

[comment]

I forgot to add:

If you're truly producing a report (as opposed to some dashboard view), then you don't need any of this. Simply find the records that fall in the next 3 weeks, sort them by product and by week (or, if all dates are on a Thursday, by date), and show them using a layout having two sub-summary parts and no body part. Then a single summary field can provide all the totals. However, weeks will be rows in this arrangement, not columns.

 

[djeans]

It took me a little while, but I got it working!!!

I can't say I understand what your calculation did, but I was able to extrapolate from your file the fields and calculations and add them to my database and get it working.

For that I am very grateful!!

 

 

[comment]

6 hours ago, djeans said:

I can't say I understand what your calculation did,

It's not that difficult: first, it calculates an absolute week number for the current week and the 2 following weeks. Then it compares the week number of the date in the Date field to the 3 week numbers: if there is a match, the corresponding Quantity goes into the matching repetition.

 

[djeans]

I've run into another snag. I need to do a calculation that includes the total amount arriving across all three weeks. For instance I need to add the value of my field "OnHand" to the total amount arriving.

I tried OnHand + PendingOrders::sTotalQtyByWeekR but that did not work. 

I also tried:

Sum ( cQtyToWeeksR [1];cQtyToWeeksR[2];cQtyToWeeksR[3]  )+ OnHand but that stops at the first related record/repetition

Any suggestions on how to make that work?

Edited September 18, 2019 by djeans

[comment]

1 hour ago, djeans said:

I tried OnHand + PendingOrders::sTotalQtyByWeekR

Try:

Sum ( Onhand ; PendingOrders::sTotalQtyByWeekR[1] ; PendingOrders::sTotalQtyByWeekR[2] ; PendingOrders::sTotalQtyByWeekR[3] )

 

[djeans]

Thank You!! 

I think that is going to work.