Need a more efficient/shorter sync time expression to pull data from another table

[StephenMattison]

In my Meetings table, [User Access] column, I am using the below expression in the Initial Value to return the data in [Access] ("User", "Admin", "Admin+") that the user has listed in their row of the Members table, for that Club. I might need a better expression that takes less time to sync, please & thank you. I also need it to only return the [Access] that the User has for that particular Club.

My expression must be all wrong because it returns a list of all [Access] for every Club that the Member is in. Normally Members would only be in one Club, but just in case they ever have the same Member email listed in multiple Clubs, I want to be sure to only return their [Access] value for the particular Club in this Row.  @Suvrutt_Gurjar You just gave me a perfect solution to keep my sync times short for a similar type of data lookup, can you give me another great recommendation here?  If you know of a smarter way to do what I'm trying to do, I'd love to hear it. Thank you very much!

1. Need expression with the fastest sync time.  2. Need to properly use [_THISROW].[Club] to return Member [Access] for the particular Club in this Row..

SELECT(Members[Access],AND([Email] = [_THISROW].[User email],[Club] = [_THISROW].[Club]))

[MultiTech]

• Assuming you've got a Current User (Slice)......
• Also assuming you've hard-reference connected the member records to the User table (in addition to the Club - main parent)....

Two steps to efficiently get this result:

1) On your Club table, create a new VC with the following formula:

INTERSECT(
  Split(Concatenate(Current_User[Related Members]), " , "), 
  [Related Members]
)

Line 2 [Related Members] = the related members list from the USER table
Line 3 [Related Members] = the related members list from the CLUB table

• This column must be a LIST column type, with a REF to the MEMBERS table
• This will return a list of all the matching member records between the "current club" and the "current user"
• From this you can then list de-reference the access values

2) Create another VC with the following formula:

Sort(Unique(
  [Current_User_Club_Access][Access]
))

Where [Current_User_Club_Access] is the column you created in the first step.

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

There's a billion different ways you could do this, utilizing the same INTERSECT() method, all depending on how you've configured your system.

• But this method doesn't re-query anything in your database; it merely calls values already there, and finds the one's that match.

[StephenMattison]

Interesting stuff, I've never seen the INTERSECT before, thanks Matt!  I'll try this & follow up.

I have an Active Members Slice & a Current Meetings Slice.

TYVM!!  Steve