8 Replies Latest reply on Oct 5, 2013 5:30 PM by disabled_jackrodgers

# Calc to determine if all portal records contain a value

Is there a single calculation (custom functions OK) that can return a boolean result if a particular field for all child records contains a value (not null)?

I normally loop through the portal records to validate that a field contains a value, but I would rather use just a single calc.

Kyle

• ###### 1. Re: Calc to determine if all portal records contain a value

Count ( ChildTable::yourFieldOfInterest ) = Count ( ChildTable::primaryKey )

• ###### 2. Re: Calc to determine if all portal records contain a value

Kyle,

I think this describes what you want.

Get all the children values in a field using the List () function.

Remove all occurrences of the first child's value.

If there is anything left then the values weren't all the same.

Let ( [

//returns all children values

all_my_children = List ( Children::field1 ) ;

//This will return only the first child's value

first_born = Children::field1 ;

//remove all first child values and returns

left_overs = Substitute ( all_my_children ; [first_born ; ""] ; ["¶" ; ""] )

] ;

Case (

IsEmpty ( all_my_children ) ; "NULL" ;

IsEmpty ( left_overs ) ; "all_the_Same" ;

"Different"

)

)

Change the words into 0 and 1 to get the results you want.

• ###### 3. Re: Calc to determine if all portal records contain a value

If empty field x, 1,0)

Then add a calculated field to your parent record that sums this field.

If sum(emptycalc) = 0, "OK'. "ERROR") or whatever you wish to use. The sum would also tell you how many portal records have null values in the field.

This assumes you mean that the field might contain "" or any value and not a specified value such as "Cat".

• ###### 4. Re: Calc to determine if all portal records contain a value

jackrodgers wrote:

• ###### 5. Re: Calc to determine if all portal records contain a value

What about repeating fields? Of course this wasn't specified and neither of us considered it. But if the field is a repeating field the referenced post would have a lot of problems. I suppose we gave a lazy answer since this should have been considred.

• ###### 6. Re: Calc to determine if all portal records contain a value

How about a SQL query of the child table for the specific matching parentID AND  testfield= ""/null ?

• ###### 7. Re: Calc to determine if all portal records contain a value

As simple as this is, it does exactly what I need.

I should have realized that Count() can't count nothing (null).

Thank you erolst (and everyone else)

Kyle

BTW, this technique will be used as part of a validation routine that alerts the users if there are issues with their data entry. For example, I can use this technique to check if every line item on a sales order has a unit price. Count() should be fast enough because the various child records wont be that numourous. How many items can a sales order have. The script runs at OnRecordCommit and checks for varoius errors and omissions and uses conditional formatting on a button to indicate that there are "Alert"s. Pressing the button shows the alerts and gives the option to ignore one (or more) of them should they not apply.,

• ###### 8. Re: Calc to determine if all portal records contain a value

More fun is to use a conditional format that colors fields that need data entry and even turns the background red if he data is incorrect.

You can use your technique to prohibit users from leaving a layout if data isn't entered.