I have two tables - Orders and OrderComponents with a one to many relationship (one order -> many ordercomponents). Both these tables are external SQL tables.
The OrderComponents table has field called DigitalDPI which is NULL by default but will have a value if the order is a digital order.
I want to add a calculation field in the Orders table - cHasDigital. I'd like the value of this calculation to be 0 by default. But if any of the related OrderComponents has a value in the DigitalDPI field then the calculation should return 1.
In summary, the calculation field in the Orders table must look at all related records in the OrderComponents table. If any of these records have a value in the DigitalDPI field then the calculation should return 1. Otherwise it should return 0.
I hope this makes sense.
A count of the DigitalDPI field should suffice. This will deliver a count of the number of related fields containing data. Your cHasDigital calculation could then be something like:
If ( Count ( relatedTable::DigitalDPI > 0 ; 1 ; 0 )