Setting up a conditional Value List between two layouts

[robroys68]

Here is my problem.

 

I have two layouts that are linked together, Master Records and Products.

I use a value list to display the status of the Master Record and Products (In work, Proofer, Final)

 

I want the status of the master record to reflect the status of multiple products.

 

So if product records 1,2,and 3 have a status of "in work", the master status is "in work".

 

I want to make it so the status of the record in the Master Records on lt goes to "Final" if all the products statuses are "Final". If one of the products is "In Work" and the other two are "Final" the Master Record record should still show "In Work"

 

Thanks

Rob

[doughemi]

How about Master Record::status =

 

Case(

PatternCount(List(Products::Status); "Proofer") > 0; "Proofer";

PatternCount(List(Products::Status); "in work") > 0; "in work";

"Final"

)

[robroys68]

Nope. I tried putting that in The Auto-Enter/Calculated Value and the Validation/Validated by calculation fields with nothing happening.

[mr_vodka]

Why is the master record's status a field? Why not a calculation?

 

 

Case ( ValueCount ( FilterValues ( List ( Products::Status ); "In work" ) ) = Count ( Products:ID ); "Final"; "In Work" )

[robroys68]

Why is the master record's status a field? Why not a calculation?

 

 

Case ( ValueCount ( FilterValues ( List ( Products::Status ); "In work" ) ) = Count ( Products:ID ); "Final"; "In Work" )

It was origionaly set up as a field in the master record because we would change the status manually.

 

Nope, that didn't work either.

[doughemi]

Nope. I tried putting that in The Auto-Enter/Calculated Value and the Validation/Validated by calculation fields with nothing happening.

It should be a Calculation field, not a text field.

 

What is the relationship between Master and Products?

[robroys68]

It should be a Calculation field, not a text field.

 

What is the relationship between Master and Products?

There is a file number that links the one Master Record record to multiple Product records.

[MartieH]

there are probably other ways, but I have done a similar task like this:

 

in the products table, create a calculated number field (I'm calling it InWorkCount) - if(statusfield="in work";1;0)

 

 

then, you need a calculated field (WorkSum) in the master table:

sum(InWorkCount)

 

finally, your Status field could be calculated as: if(WorkSum >=1; "In Work"; "Final")

 

the logic is that you are counting the number of child records that are 'in work' - as long as there is 1 of them, the master record shows that it is 'in work'. If there are no child records showing a status of 'in work', the master record will show that it is 'final'.

 

hth,

Martie

[mr_vodka]

If you want to manually override the field then I suggest using another field in the parent to manually enter in the status. Then the calc could be adjusted to something like:

 

 

If ( not IsEmpty ( manualstatus ); manualstatus;

         Case ( ValueCount ( FilterValues ( List ( Products::Status ); "In work" ) ) = Count ( Products:ID ); "Final"; "In Work" )

   )

 

 

 

Otherwise you will get into a conflict issue of whether to change the parent status or not if you change a child record's status. Should it always change when any related child status is updated? If so you can have a trigger update the parent status using a simple self join of the child table keyed on the child foreign key. Then you can use a similar calc that I gave but referencing the self join TO ( Products 2 ).

[robroys68]

there are probably other ways, but I have done a similar task like this:

 

in the products table, create a calculated number field (I'm calling it InWorkCount) - if(statusfield="in work";1;0)

 

 

then, you need a calculated field (WorkSum) in the master table:

sum(InWorkCount)

 

finally, your Status field could be calculated as: if(WorkSum >=1; "In Work"; "Final")

 

the logic is that you are counting the number of child records that are 'in work' - as long as there is 1 of them, the master record shows that it is 'in work'. If there are no child records showing a status of 'in work', the master record will show that it is 'final'.

 

hth,

Martie

I have more than two statuses.

If you want to manually override the field then I suggest using another field in the parent to manually enter in the status. Then the calc could be adjusted to something like:

 

 

If ( not IsEmpty ( manualstatus ); manualstatus;

         Case ( ValueCount ( FilterValues ( List ( Products::Status ); "In work" ) ) = Count ( Products:ID ); "Final"; "In Work" )

   )

 

 

 

Otherwise you will get into a conflict issue of whether to change the parent status or not if you change a child record's status. Should it always change when any related child status is updated? If so you can have a trigger update the parent status using a simple self join of the child table keyed on the child foreign key. Then you can use a similar calc that I gave but referencing the self join TO ( Products 2 ).

For this would I also make a summery field?

[mr_vodka]

Not unless you want to do some aggregation or other summary fields traits.