Need help with a query

[justyniano]

Hi,

So, i have a table called Novedades, with a subtable that refferences Novedades called Aceptacion de comunicaciones, this last table also refferences a 3rd table called Socios. Within a row in the 3rd table i need to see if there are any Novedades that dont have any refferenced rows in Aceptacion de comunicaciones vinculated to the "Socio" from the 3rd table.

Basically i need to see if there are any comunications not accepted by the user at the moment. This acceptances are in the table Aceptacion de Comunicaciones.

The worst thing is that i had solved this problem, but erased the virtual column in order to solve an other one and i cant get it to work againg. Heres what i have been working with, with no luck:

COUNT(
select(
Novedades[IdComunicado],
AND(
[tipo] = "Comunicado importante",
ISBLANK(
select(
Novedades[Aceptacion de comunicados][IdComunicado],
[socio] = usersettings(socio)))))

THANKS IN ADVANCE!!!

[justyniano]

Table NOVEDADES

IdComunicado

Fecha

Tipo

Titulo

Descripcion

Tags

Comunicador

Link

Table Aceptacion de Comunicados

Id

Comunicado

Aceptacion

Socio

Fecha aceptacion

Comunicado is a ref to NOVEDADES. Socio is compared with usersettings(socio).

[Suvrutt_Gurjar]

For a better response, you may want to share the screenshots of relevant columns and their types of the three tables, something like below with one line description of the reference and other relevant columns.

[Order ID] references Orders table. [Product ID] references Products table and so on.

[justyniano]

Here:

[Suvrutt_Gurjar]

Thank you. Please add screenshots columns of Socio table. Also 

Also could you update how do you consider acceptance done in the "Aceptacion de comunicaciones". Is it based on the column [Aceptacion] being filled or non blank and [Fecha aceptacion] being non-blank? 

Also is the row in the  table "Aceptacion de comunicaciones" already created with ref columns [Socio] and [Comunicado] filled in and once the user fills in [Aceptacion] and  [Fecha aceptacion] , then you consider the record as accepted?

[justyniano]

the column "New Virtual Column" is where im triying to insert the formula.

I consider the acceptance when the field aceptacion is blank or different than "Aceptado".

The field "aceptacion" is filled with a button that has a "behaviour" that creates a new row in table "Aceptacion de comunicaciones" with the ID of [socio] and the ID of [IDcomunicado].

This should return if any user, based on the usersettings(socio) value, has any pending communicationes (novedades) that havent been accepted.

Thanks for your help!

[justyniano · Accepted solution]

Solved! 

COUNT(
FILTER(
"Novedades",
AND(
[IDcomunicado] = [_THISROW].[IDcomunicado],
[tipo] = "Comunicado importante",
ISBLANK(
FILTER(
"Aceptacion comunicados",
AND(
[socio] = usersettings(socio),
[comunicado] = [_THISROW].[IDcomunicado]
)
)
)
)
)
)

this is the code, and i check if it is true with: 

sum(novedades[comunicados no aceptados])>0

its not elegant, but it work. Thanks for your help anyway!