Getting information from all Children

[gephry]

I need to be able to update the status of an order by getting information about all related portal records (basically children) pertaining to that order.

Explanation:

1. I have two tables, Orders, and Bins.

2. One "Order" has many "Bin" items assigned to it.

3. Each Bin item has its own status (one of three: "Duplicating, Labeling, Complete").

4. The Order itself also has a status ("Processing, Warehouse, Shipped").

My relationship OrBin_SerialNumber is:

Orders::RECORDSERIAL_Number <===> Bin::RECORDSERIAL_NumberOrder

Now, in order to update the status of the Order, I need to check the status of all the related Bin items to see when all of those Bin items are Complete (so the Order can change to status "Warehouse").

I tried the PatternCount() function with the data Extend( OrBin_SerialNumber::Status_Bin ) but for some reason it's only returning the status one of the many Bin items in that order.

What can I do to get the statuses of all the related Bin items?

Thanks!

[David Jondreau]

Do you want a field in one table to change automatically, based on fields in a related table? That's not easy to do.

If you just need a flag of some sort, then creating a numeric calc field in Bins StatusNum = If(Status = "Complete"; 0;1). All completed Bins will have a value of 0. In Orders a field BinSumStatus= (Bins::StatusNum) will tell you if all the Bins are complete or not (0 or >0).

The catch here is that because you're summing a related table, the Status can't be stored which makes it hard to search on and won't automatically update your Order status.

[gephry]

No, it doesn't have to change automatically. Each time a Bin is updated, more data than just the status needs to be changed. So I have a script updating the Bin and putting all the pertinent info into the Bin (who updated it, when, where, etc). So, I figured that when the Bin status would change, it'd check the other Bin items for that Order and if they were all Complete it'd update the Order status, too.

And that is where I ran into the problem with the Extend(). I'm trying to get all the status data for the Bins pertaining to the current Order but it's returning only one status out of many Bin's statuses.

So, in essence, no, it doesn't need to update automatically. It could be nice, but considering the other things I need it to do simultaneously, it's easiest to do it in a script....but I will kick your idea around in my head. Who knows, maybe it will work.

I appreciate the post, thanks.

[gephry]

Ooo, nice. Actually, I can use that. I was hoping for a script-only way to keep the size of my database down, but the Extend isn't working and I can't think of another function off the top of my head (I'm programming-fried today).

I modified my scripts so instead of the non-working Extend, it checks to see if that BinSumStatus calculation is > 0. If not, it marks the Order as "Warehouse" and does all the other things I need it to do also. So that will suffice.

I am curious however to see of any other solutions people have. Maybe something that doesn't require calc fields?

Thanks again.

[David Jondreau]

Oops. I just realized a major typo...

>>In Orders a field BinSumStatus= (Bins::StatusNum) will tell you >>if all the Bins are complete or not (0 or >0).

That should be BinSumStatus = Sum(Bins::StatusNum)

You want to add all the StatusNum together, if they're all complete you get zero. Whatever the number is the the number of Bins that aren't Complete.

But if you're doing it by script anyway...well there's probably a way to accomplish this that would give you an easily searchable field. Like adding the following script steps to the end of your script:

If Sum(Bins::StatusNum)

Set Field Orders::Status = "Warehouse"

End If

Or some such.

G

[gephry]

Grip--

No problem dude, I understood immediately what you meant. And yes, that's exactly how I incorporated it. I have the Orders::Status field which allows me to search, filter in portals/etc. So yea, it's all good.

Thanks to a new fresh day, your solution gave me an idea that's going to be more flexible and require less fields. In the Order table, if I create one field using the List() function instead of two calc fields using a Sum(), it will be much more flexible. Thus:

Fields

already have: Bin::Status

create: Orders::calcBinStatusList = List( OrBin_SerialNumber::Status )

Then I use PatternCount( calcBinStatusList ) in my script and I can count any or all statuses of all the related Bins. That will allow me to manipulate more data and allow expansion for in the future. I can know exactly how many Bins of each status per Order.

So I can eliminate the flag on the Bin side:

creating a numeric calc field in Bins StatusNum = If(Status = "Complete"; 0;1).

Thanks for all your help!