Dereference an Id to get another field from that table for template

Help me.
I have a table with an enumlist field called [Actividades], in it I keep the ID (IdRecurso) of the records of another table called Resources. This has the field that interests me, called [Nombre del recurso]

Yes I use it
[Actividades] [IdRecurso] prints me the IdRecursos, but if I use [Actividades] [IdRecursos] [Nombre del recurso]

it does not execute the template, probably by mistake.

Solved Solved
0 55 530
1 ACCEPTED SOLUTION

Creo que la รบnica forma serรญa creando items con otra expressiรณn Start: dentro de la anterior y que cada uno sea una fila.
Serรญa mรกs sencillo agregando un <br> pero la plantilla ignora esos tags cuando vienen de una fรณrmula

View solution in original post

55 REPLIES 55

Aurelien
Participant V

Hi @Juan_Francisco_Calv1

As far as I know, list dereferencing works for 2 terms, not 3.
Iโ€™m not sure I fully understood your table structure, but based on this:

In order to keep it simple, I would suggest something like:

  1. Add a new virtual column named โ€œRecursosโ€, type Ref, source table Recursos, with expression:
SPLIT(
  CONCATENATE([Actividades][IdRecursos]),
 " , ")

(because [Actividades] is an EnumList, so technicallly not really a List ==> this one will actually be a List)

  1. Use this expression in your template:
[Recursos][Nombre del recurso])

Let us know if that works for you

For reference:

HI

Thanks for your support.

Itโ€™s wrong. It says that it cannot be of type Ref. It is corrected if I put type List, but it does not work in the template

Oops, my mistake, sorry. It will be a List of Ref.

It works well

tks

Turns out it only shows the first item in the list and not the others

What happens when you click there on each expression in your table ?


Can you try to re-create expression from your template in your table, so that you can evaluate which expressions return the expected results ?
Also, here on Data/Table panel, you can see each calculation results at once:
3X_a_8_a8093219a0a75bfc943536715eee0d4c1443364a.png

Hello

When creating the List and Ref of Resources and References type field, if the lists are created correctly, this can be seen in this image.

The problem is that when generating the pdf with the template it does not display all the records. In the case of the previous image you can see two records for each of the lists, but in the pdf only one of each of the lists.

3X_7_f_7f6fbf97e61c27c683451b0a2cf17b75ff4336c9.png

There is something that I must be missing in your explanation.
Just to make the output clearer, can you add : -LIST("") at the end of each expression in your template ?

That would become:

Anotaciones
<<[Actividades][Anotaciones]-LIST("")>>
Materiales
<<[Actividades][Materiales]-LIST("")>>
and so on...

That will remove empty values from the output.

I allow myself to call @Steve @Suvrutt_Gurjar @SkrOYC on rescue on this case, I donโ€™t get why you only have one item getting out of your expression.

Okay, Iโ€™ll add what you tell me, I didnโ€™t know how to do that.

Hello
I tried your method again and it worked. I removed the whites from the Split and thatโ€™s it

SkrOYC
Participant V

I will speak in spanish since thatโ€™s @Juan_Francisco_Calv1โ€™s main language as far as I can see.

Hola Juan, tratarรฉ de ayudar.
Sobre la columna [Referencias], parece ser del tipo List, cierto?
Podrรญas tratar con lo siguiente?

<<SELECT(
LA_TABLA_DE_RECURSOS[Referencia completa],
  IN(
    [IdRecurso],
    [Actividades][IdRecurso]
  )
)>>

No estoy 100% seguro de que va a funcionar pero podrรญas intentarlo en lugar de `<<[Referencias][Referencia completa]>>

Hola Oscar
Ahora mismo voy a probar y te cuento.

No funcionรณ:(
Te adjunto imรกgenes


3X_3_1_3154fc9ec0de9c9ff71609927ecb99b49b6878f1.png

Regresemos al inicio.

  • Yo tengo una tabla Sesiones. En ella tengo un campo Actividades tipo Enumlist-Ref que guarda el IdActividades de la tabla Actividades.

  • En la tabla Actividades hay dos campos de tipo enumlist-Ref. Uno es Recursos y el otro es Referencia. Ambos guardan la llave de sus respectivas tablas.

  • A mรญ me interesa desplegar de la tabla Recursos el campo Recurso Completo y de la tabla Referencia el campo Referencia Completa

  • Dado que la desreferenciaciรณn de un campo tipo Enumlist-Ref funciona solamente para dos campos, solo puedo desplegar en mi plantilla [Actividades][IdRecurso], cuando lo que necesito idealmente serรญa [Actividades][IdRecurso][Recurso Completo] y algo similar para la tabla de referencias.

Me cuentas si me di a entender, por fa.

Como se ve en la imagen, al desreferenciar sรญ muestra todos los datos.

We go back to the beginning.

  • I have a Sessions table. In it I have an Activities field of the Enumlist-Ref type that stores the ActivityID of the Activities table.

  • In the Activities table there are two fields of type enumlist-Ref. One is Resources and the other is Reference. Both keep the key to their respective tables.

  • I am interested in displaying the Full Resource field from the Resources table and the Full Reference field from the Reference table

  • Since the dereferencing of a field type Enumlist-Ref works only for two fields, I can only display in my template [Activities] [Resource Id], when what I need ideally would be [Activities] [Resource Id] [Complete Resource] and something similar for the crosstab.

Tell me if I made myself understood, please.

As seen in the image, dereferencing does show all the data.


Hi, can you please copy-paste english translation as well ? Iโ€™m interested in your situation getting solved
(and that can help other people in the future)

With pleasure. I will edit the message, adding the translation and images

SkrOYC
Participant V

We are gonna keep it in english since it will be more helpful in this context.

So:
Sesiones has Enumlist/Ref to Actividades
Actividades has Enumlist/Ref to Recursos and another one to Referencia

Please post your Star: expression.

Assuming itโ€™s returning a list of keys from Sesiones, please use:

<<SELECT(
  Referencia[Referencia completa],
  IN(
    [IdReferencia],
    SELECT(
      Actividades[Referencia],
      IN(
        [IdActividades],
        [_THISROW].[Actividades]
      )
    )
  )
)>>

Change your <<[Actividades][IdReferencia]-LIST("")>> with this one

PS: Change your column/table names accordingly

Me perdรญ, amigo.
En la tabla Sesiones tengo un Enumlist/Ref a la tabla Actividades.
En la tabla Actividades tengo
Un Enumlist/Ref [IdRecurso] a la tabla Recurso, en donde estรก el campo que quiero que es [Recurso completo]
Otro Enumlist/Ref [IdReferencia] a la tabla Referencias, en donde estรก el otro campo que quiero que es [Referencia completa]

Podrรญas reescribir la instrucciรณn tomando en consideraciรณn esto, pues dado que son Select anidados me perdรญ.

En la plantilla deberรญa estar algo como esto:

Ahora mismo pruebo y te cuento.

Tiene algรบn error porque no genera el pdf

Hay un | antes del IN()

Era el cursor, pero no tiene ningรบn otro sรญmbolo

Puedes ver que aparece en la App Monitor sobre ese bot?

Cรณmo hago eso, disculpa

Bots tienen esta opciรณn:
3X_f_c_fca7ec9d92bff5c604eebc5f60040091748dc250.png

Es una app para monitorear el estado de cada tarea.
Desconozco si es universal, prueba este enlace:

Listo

Ya estoy en la opciรณn Manage y en la pestaรฑa monitor. Me aparecen estas opciones

Under Recursos:

SELECT(
  Recursos[Nombre del recurso],
  IN(
    [IdRecurso],
    SELECT(
      Actividades[IdRecurso],
      IN(
        [IdActividad],
        [_THISROW].[Actividades]
      )
    )
  )
)

Under Referencias:

SELECT(
  JabRef[Referencia completa],
  IN(
    [Identifier],
    SELECT(
      Actividades[IdReferencia],
      IN(
        [IdActividad],
        [_THISROW].[Actividades]
      )
    )
  )
)

After seeing your tablesโ€™ schema, it should work.

Also, take into consideration the following: make better table and column names. It should be easier to troubleshoot and also make expressions

Thank you very much for addressing this issue. I will take your recommendations very seriously. Right now i try

Too bad, keep bothering with this!
I tried with the Resources table and it did not generate the PDF

But your Monitor app says itโ€™s โ€œCompleteโ€
Check if you can find it in your GDrive/OneDrive
This may be another problem different from the first one

It is not in another location, it just does not generate it. I remove the instruction and it generates it well

Mmm.
Can you post an updated screenshoot from Monitor app?
Also, please post your Start: expression

Iโ€™m really confused since you said that itโ€™s not generated but the monitor apps shows everything as โ€œCompleteโ€

Imagina cรณmo estoy yo
Borro la instrucciรณn y genera el pdf bien, sin problema

Mira el PDF sin la instrucciรณn

Top Labels in this Space