Jump to content

Getting information from all Children


gephry
 Share

This topic is 5788 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This topic is 5788 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.