How to exclude a value from the list in ref field?

[Mark_Holanda]

hi guys,

im quite lost here. I have an attendance form to record attendees of a particular event. What i want to achieve is if the member is already added as attendee, i dont want his name to appear on the list dropdown.

Can you please help me? thank you in advance.

Here are the screenshots of my tables:
Attendance table - https://www.screencast.com/t/TeqbjlGs
Members table - https://www.screencast.com/t/baVfy9J5ALVz
Events table - https://www.screencast.com/t/KOr484or

Attendance form - https://www.screencast.com/t/rQnMctQWi

regards,
Mark

[WillowMobileSys]

In the Ref column definition, there is a Property named “Valid_If”. This is also used to determine the values that should show in the dropdown.

To exclude values you will need to insert an expression (or modify the expression if you have one there already) to identify the REMAINING values you wish to show.

In your case you will want an expression similar to:

SELECT(Members[Member ID], 
       OR( 
          NOT(
              IN([Member ID],
                 SELECT(Attendance[Member ID], 
                        [Event ID] = [_THISROW].[Event ID]
                 )
              )
          ),
          [_THISROW].[Member ID]
        )
)

Basically, select all members who are not already assigned this event OR is the member assigned to this row. This last part is to make sure the member shows when EDITING the row.

NOTE: This is not tested and may have some syntax errors

[Mark_Holanda]

WillowMobileSystems:

SELECT(Members[Member ID], OR( NOT( IN([Member ID], SELECT(Attendance[Member ID], [Event ID] = [_THISROW].[Event ID] ) ) ), [_THISROW].[Member ID] ) )

Thanks John… i will try this now and i will let you know. Thanks so much!

[Mark_Holanda]

hi John,

I got this error when i pasted your code https://www.screencast.com/t/PghGsD72a

I tried to fix the column name but i got this new error https://www.screencast.com/t/BJqqiqt8a

Any idea?

[WillowMobileSys]

Yes, sorry. This part:

`[_THISROW].[Member ID]’

should have been this instead (with column name corrected as well):

`[UID] = [_THISROW].[UID]

[Mark_Holanda]

Hi John,

Thanks again. I tried your code again but fixing the last UID to Member ID so the final code looks like this:

SELECT(Members[UID], 
       OR( 
          NOT(
              IN([Member ID],
                 SELECT(Attendance[Member ID], 
                        [Event ID] = [_THISROW].[Event ID]
                 )
              )
          ), [UID] = [_THISROW].[Member ID]
        )
)

The result of the test is this https://www.screencast.com/t/4PeEGo0HA6 (not sure if that looks right) but when i tried to test the form, all of the members are still there on the list.

Any idea?

[Mark_Holanda]

hi John,

I was able to resolved the issue. heres the final code:

SELECT(Members[UID], 
   OR( 
      NOT(
          IN([UID],
             SELECT(Attendance[Member ID], 
                    [Event ID] = [_THISROW].[Event ID]
             )
          )
      ), [UID] = [_THISROW].[Member ID]
    )

)

Thank you for your help. i will not able to resolve it without you. Thanks again!

[WillowMobileSys]

Glad you got it figured out! I obviously wasn’t keeping the column names straight!

[Mark_Holanda]

Hehe… it happens a lot especially if you have so many things in mind… I was trying to understand the expression thats why i play around on the column names… hehe

Thanks again mate!

[Mark_Holanda]

hi John,

I need your help again… how can i filter the list on the dropdown NOT TO SHOW past events?

Gathering table - https://www.screencast.com/t/Lgb9Jnoqe
Events table - https://www.screencast.com/t/KOr484or
Attendance form - https://www.screencast.com/t/rQnMctQWi

thank you in advance.

[WillowMobileSys]

Mark_Holanda:

SELECT(Members[UID], 
   OR( 
      NOT(
          IN([UID],
             SELECT(Attendance[Member ID], 
                    [Event ID] = [_THISROW].[Event ID]
             )
          )
      ), [UID] = [_THISROW].[Member ID]
    )
)

Starting with your final expression above, you would modify it like the below. Instead of “NOT TO SHOW past events”, this is implemented to check attendance against current and future events.

SELECT(Members[UID], 
   OR( 
      NOT(
          IN([UID],
             SELECT(Attendance[Member ID], 
                    AND(
                        [Event ID] = [_THISROW].[Event ID],
                        [Date] >= TODAY()
                    )
             )
          )
      ), [UID] = [_THISROW].[Member ID]
    )
)

[Mark_Holanda]

hi John,

thanks for the reply. actually this is a different field than the first one… the previous one is for the member’s dropdown… this time this is for the event dropdown…

hope that makes sense.

[WillowMobileSys]

Yes, I should have know better! Ugh!

Same idea just against the Events table. Show only current and future events.

So to filter out past events in the Event dropdown:

SELECT(Events[EVID, [Date] >= TODAY())

[Mark_Holanda · Accepted solution]

so i just the change the greater than to less than symbol…

heres the final code:

SELECT(Events[EVID], 
   OR( 
  NOT(
      IN([EVID],
         SELECT(Events[EVID], [DATE] <= TODAY()             	
         )
      )
 	), [EVID] = [_THISROW].[Event ID]
)	
)

thanks again mate!

[WillowMobileSys]

Works but in this case, the expression is less efficient than it needs to be and you have a “double negative” type thing going on.

Since you don’t need to worry about an Event that has already been selected (like you did for members), you could use the expression directly as I had written and I believe it would get you what you need.

[Mark_Holanda]

Yes this works as well:

SELECT(Events[EVID], [Date] >= TODAY())

thanks again mate! I appreciate your help

[Mark_Holanda]

WillowMobileSystems:

SELECT(Events[EVID, [Date] >= TODAY())

I have this code:

SELECT(Events[EVID], 
   OR( 
      NOT(
          IN([EVID],
             SELECT(Events[EVID], [DATE] >= TODAY()             	
             )
          )
     	), [EVID] = [_THISROW].[Event ID]
    )	
)

but the result is the other way around… hahaha