CODEWITHSUNDEEP
c#datatablemaster-detaildatarelation
G Bradley MacDonald
G Bradley MacDonald

Reputation: 125

.NET DataColumn.Expression - use of child prefix - problem with IsNull

I have an interesting problem with a master/detail table relationship.

I read in the Master table and the Detail table into DataTables in C#.

I create a data relationship, and it ties the two tables together. In the detail table, I have a blob field that may be either Null or contain some text.

I have a calculated field in the master DataTable that is boolean and I want it true if any of the child records is not null, and false if they are all null.

I was hoping to do this using the DataRelations property of the DataColumn object - but it does not allow me to specify IsNull(Child.Field).... It says that the Child. reference is not allowed with the IsNull function.

So I am wondering how others would deal with this? I was trying to avoid using another query to populate the master True/False field - but I may have to go down that route.

Am I missing something with the DataRelationships and the datacolumn.expression idea?

Simple Table structure - just for reference.

MasterTable

  QryId  Int
  AreChildrenNotNull  Boolean

DetailTable

  QryId  Int
  DetailId Int
  TextField  Blob

Upvotes: 0

Views: 35

Answers (2)

G Bradley MacDonald
G Bradley MacDonald

Reputation: 125

Ok - thanks to the jmcilhinney comment - it got me thinking. I found a solution - although it is not working exactly the way I want/need it to.

  1. Create a new calculated field in the Detail table - with expression like Len(Child.TextField) This will put a number in that field for each child record.
  2. In the calculated field in the Master table - use the following expression IIF (Sum(Child.CalcLenField) > 0, true, false) This causes a summation of the Lengths of all the child records. If any one of them has text in this field - then it sets the value of the calculated field in the Master to TRUE.

Now - this part was exactly what I was hoping to do - but it only gets calculated when I move the to the active record in the grid attached to the Master table. This makes sense - but not what was I was trying to do. I need to have the boolean value in the Table showing in the grid all the time - not just when I move to that record... I need to know which Master records have at least one Detail record with text in that field...

I guess I will have to create a calculated field and populate it in code when ever the table is loaded - or either the Master or Detail table are changed. Many thanks to those who took time to comment!

Upvotes: 0

jmcilhinney
jmcilhinney

Reputation: 54457

This is from the relevant documentation:

A column in a child table may be referenced in an expression by prepending the column name with Child. However, because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function. For example, Sum(Child.Price) would return the sum of the column named Price in the child table.

ISNULL is not an aggregate function, hence you can't use Child there.

I haven't tested but what you may be able to do is to use Child within the Min or Max function. If those functions work on numbers only then you're out of luck. If they will give you the first and last values in a sorted list of strings then you should be able to use one or the other to get a single value and then pass that to ISNULL. Which you would use would depend on whether NULL values are sorted before or after other data. Something like this may work:

NOT ISNULL(MAX(Child.TextField))

If that doesn't work then I think you're out of luck.

Upvotes: 1

Related Questions