Get specific value from table by a field name

[#1]

Hello ,

I have this tables in my project:

Main_Table:

[Part Family Name]	[Child1]	[Child2]	[Child3]	[Type]	[Type #]
AA1111	AA1	AA12345	AA6548	M1	1
BB2355	BB98744	BB65432	BB6581111	M1	2
CC24	CC55555	CC874654	CC988877	M2	1
DD235488	DD88452	DD46888	D2	M2	2

More_Details_Table:

[Part Name_A]	[Type]	Some other more columns...
AA1	M1	...
AA12345	M1	...
AA6548	M1	...
BB98744	M1	...
BB65432	M1	...
BB6581111	M1	...
CC55555	M2	...
CC874654	M2	...
...	...	...

Balance_Table:

[Part Name_A]	Balance
AA1	200
AA12345	11
AA6548	100
BB98744	8
BB65432	81
BB6581111	82
CC55555	83
CC874654	84
...	...

* The Main_Table connected to More_Details_Table by the Type field (key).

* The More_Details_Table connected to Balance_Table by the [Part Name_A] field (key).

I created some straight table with:

1 Dimension: [Type], [Type #]

3 Expressions

* My selection is on the [Type] field, for example I selected and filtered the Types and show only M1 Type:

The Straight table now looks like this:

[Type]	[Type #]	First Level	Second Level	Third Level
M1	1
M1	2

On the expressions I'm trying to display this content:

On each cell I want the associated Child, for example on First Level column:

[Type]	[Type #]	First Level	Second Level	Third Level
M1	1	AA1
M1	2	BB98744

Additionally, I need to add a second row on each cell that contain the balance that associated to the specific Child name, for example:

[Type]	[Type #]	First Level	Second Level	Third Level
M1	1	AA1 200
M1	2	BB98744 8

I tried to write something like:

=[Child1] & Chr(13) &

only({$<[Part Name_A] = p([Child1]) >} [Balance])

but got only the Child name (AA1 on the first cell and BB98744 on the second)...

Also I tried to use FieldValue function but the position of each value changed when I have lots of Types...

please help me with this...

This is the final table I need to get (after selected and filtered the Type field to 'M1' value):

[Type]	[Type #]	First Level	Second Level	Third Level
M1	1	AA1 200	AA12345 11	AA6548 100
M1	2	BB98744 8	BB65432 81	BB6581111 82

Many Thanks for your support,

Itsik

[Anonymous]

You are welcome Itsik, I will be waiting for new data then.

Maybe you could send me just a little records, where the formula is not working.

Regards