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]

See if this helps..

=[Child1]  & Chr(13) &  MaxString({$<[Part Name_A] = p([Child1]) >} [Balance])

or

=[Child1]  & Chr(13) &  Max({$<[Part Name_A] = p([Child1]) >} [Balance])

[#3]

This returns me the same balance (the last child balance) to the two cells...

like this:

[Type]     [Type #]      First Level     Second Level     Third Level

---------------------------------------------------------------------------------------------

M1          1               AA1         

                               8

M1          2               BB98744

                                          8

[Anonymous]

Try this.

Hope it helps

[Anonymous]

Hello Itsik.

Try

Child1 & Chr(13) & FieldValue('Balance',  FieldIndex ( 'PartName_A', Child1 ))

[#6]

Hi Lucas,

The expresiion gives me some wrong balance values...

The strange thing is when I put all the expression:

=Child1 & Chr(13) & FieldValue('Balance',  FieldIndex ( 'PartName_A', Child1 ))

I got some right values of the Child1 before Chr(13) but wrong values at the balance...

And if I cut the begining of the expression and write only:

=FieldValue('Balance',  FieldIndex ( 'PartName_A', Child1 ))

I've got the same balance at two cells a wrong one, and different from the two that was before with the complete expression...

[Anonymous]

Hello Itsik,

That is weird, with the examples that I got  from your model, I could reach the exact result that you desired.

Please, try loading your datamodel in the Attached file ( has the same columns names as yours ), to see if you get any wrong result.

[#8]

I'll Do one more check and Upload my file if it dont success...

[Anonymous · Accepted solution]

Ok Itsik,

Here is what are the values on my example ( and I attached the file again, just in case 😞

Type	Type #	First Level	First Level -   Balance	Second Level	Second Level - Balance	Third Level	Third Level -   Balance
M1	1	AA1   200	200	AA12345   11	11	AA6548   100	100
M1	2	BB98744   8	8	BB65432   81	81	BB6581111   82	82
M2	1	CC55555   83	83	CC874654   84	84	CC988877
M2	2	DD88452		DD46888		D2

[#10]

Your example is really work, but in my project, with my data it didnt... I dont know why...

I'll check with my company if I can upload some of the real data to show you...

Thank you for everything so far...