ORDER BY and TAKE BEST in Where for rule?

nlarson

It seems like adding “ORDER BY bo.attribute DESC' and 'TAKE BEST 1” are incompatible with BO rules when a WHERE is present. I was wondering if that is by design?

Below is the real way I was trying to use it. Was hoping to avoid moving this into a process with a very complex THIS and THAT logic by using AVG of a single result as the value used to update a reference e object.

Measurements.MetricsReference.LastMeasureDate= AVG Measurements.DueBy WHERE (Measurements.MetricsReference=ThisMeasurements.MetricsReference AND Measurements.Confidence IS DEFINED ORDER BY Measurements.DueBy DESC TAKE BEST 1)

I tried a few ways to tackle this, and was hoping that it's maybe just a syntax error?

nlarson

So to provide some more info on this. I have a Parent BO Instance (Metrics), when it is created 12 Child BO Instances (Measurements)are creating as place holders for monthly data entry. I have a rule which updates a date field in the parent when conditions are matched in the child: it fires this action:

Measurements.MetricsReference.LastMeasureDate=ThisMeasurements.DueBy

In a vacuum this works fine, as the user fills in data over the year the measurement date progresses towards the end. However… in real life this method can be problematic since occasionally, a user goes ‘back’ to revise some older info: when that happens the LastMeasureDate (and it’s subsequent calculations) are thrown out of whack. The current work around is to ask users to then go and make a change to the correct Measurement to fix the date. Obviously that is cumbersome so what I would like to do is create a rule or process which selects ‘the right’ Child.

Ideally I will trigger a rule/process which will find the child BO instance with a date closest to, but not greater than current_date. That item would be used to form that context and thus provide the value to update the parent reference attribute. I was thinking could use a rule like the one below to ‘trick’ the system into picking the correct value

However, per my original post that did not work. So I then tried 2 processes, but I could not resolve the value using This or That. Here is one flavor of the process called from the parent:

FIND Measurements WHERE Measurements.MetricsReference=ThisMetrics AND Measurements.Confidence IS DEFINED ORDER BY Measurements.DueBy DESC TAKE BEST 1
Metrics.LastMeasureDate=ThatMeasurements.DueBy

I tried a few variants and some this/that combos to no avail. I am about to split the process into two process to see if that works, but I feel like I might be trying to swat a fly with a sledge hammer, so if anyone has some ideas ot other approaches on how to tackle this I am all ears.

tford

FIND Measurements WHERE Measurements.MetricsReference=ThisMetrics AND Measurements.Confidence IS DEFINED ORDER BY Measurements.DueBy DESC TAKE BEST 1
Metrics.LastMeasureDate=ThatMeasurements.DueBy

Have you tried it without THIS and THAT prefixes?

rocketman

Hows about

FIND Measurements WHERE (Measurements IN Metrics.whatever the ref field is called AND Measurements.Confidence IS DEFINED) ORDER BY Measurements.DueBy DESC TAKE BEST 1

nlarson

Thanks guys, I will give these a try today! If this does not work then I have worked out a petty solid alternate approach. It's not quite prefect but an 90% improvement.

nlarson

Yep, turns out that sledge hammer is no good for swatting flies... removing the This/That got this working as desired. Thank you so much guys!