New column in a table based on another table in a one-to-many relationship

[AliceW]

Hi everyone,

I can't figure this out, and so your help would be very much appreciated.

What I have:

I have one table, 'Account', in a one-to-many relationship with another table, 'Opportunities'.

There are two relationships, both one-to-many:

- Account ID to Billing Account ID (active relationship);

- Account ID to Supported Account ID;

An Account ID in the 'Account' table can have zero, one, ore more Opportunities.

What I need:

Another column in the 'Account' table which gives a 'Yes' when the Account ID is linked to at least one Supported Account ID and 'No' when it does not.

What I've tried:

RELATED and USERELATIONSHIP. The latter because I need to use the non-active relationship.

Could a table function work? If the Supported Account ID count is >0 or not blank, then 'Yes'? They are my weak point.

Big thanks in advance,

Alice

[AliceW]

It was such a big blocker for me - I really appreciate you taking the time to help me out.

[littlemojopuppy]

De nada...glad I could help!

[littlemojopuppy]

Try this...

IF(
	COUNTROWS(RELATEDTABLE(Opportunities)) > 0,
	TRUE(),
	FALSE()
)

[AliceW]

Thank you! Any thoughts on how to implement USERELATIONSHIP? I need to use the non-active relationship between the tables...

[littlemojopuppy · Accepted solution]

Certainly...

IF(
	CALCULATE(
		COUNTROWS(RELATEDTABLE(Opportunities)),
		USERELATIONSHIP(I don't know the keys so insert here)
	) > 0,
	TRUE(),
	FALSE()
)

[AliceW]

THANK YOU, Mojo!!