Solved: Filter entries with ref types

Diarmuid_O_Brie · 02-24-2021 01:02 PM

I need to cross reference entries between tables using Ref data type, as it allows me to relate the records between the tables and group them.
What I need to do, but unable, is filter which records are to be presented. I have the following scenario, and two questions:

I have a booking form [StartDateTime, EndDateTime, User, Resource]. The user field cross-references a user list [ User table with id (key), First Name, Last Name, Nickname, isActive] via a dropdown selection and x-ref using ‘ref’ data type

the user list has some non-active members [isActive = N]. How do I filter the available options in the dropdown menu to only active members [isActive = Y]?
When I view the user ‘card’ I have a list of all bookings in the system under each name, appended to that user. How do I filter them to only show future bookings [StartDateTime] > NOW().

I looked at some examples on here but it isn’t clear to me, such as:
Ref List Filter (appsheet.com)

any advice greatly appreciated. thanks
Diarmuid

Steve

In Valid If:

(
  FILTER(
    "user list",
    [isActive]
  )
  + LIST([_THIS])
  - LIST("")
)

View solution in original post

Steve

In Valid If:

(
  FILTER(
    "user list",
    [isActive]
  )
  + LIST([_THIS])
  - LIST("")
)

Diarmuid_O_Brie

Thanks a lot Steve. That worked a treat. I would never have derived that on my own.

Regarding the other issue, the user table has a VirtualColumn automatically added to relate bookings assigned to that user through the use of the Ref data type, and I only want to show future bookings in the ‘inline’ view. I have since added a Boolean VC to my Bookings table called [inFuture] with the simple formula " [StartDateTime] > now() ", giving me a Y/N for each line.
I assume I need to use this [inFuture] parameter in the aforementioned user table VC to filter the cross reference list for future bookings, but how?
This is currently defined simply as REF_ROWS(“Bookings”, “User”), which gives a list of the record indices, [idBookings] = key.
many thanks

Steve

The best way to handle this is to create a new virtual column with an App formula expression like this:

FILTER(
  "Bookings",
  AND(
    ISNOTBLANK([User]),
    ([User] = [_THISROW]),
    ISNOTBLANK([StartDateTime]),
    ([StartDateTime] > NOW())
  )
)

Diarmuid_O_Brie

Outstanding Steve, thanks a lot. Appsheet was clever enough to pick up this VC automatically.

One small question …

how can I update the above equation to sort the list in ascending order based on [StartDateTime], so the next chronological record is always presented first?

thanks again

Steve

You wouldn’t update the expression: the sort order of the inline list is determined deck, gallery, or table view in the ref position for the table referenced.

Diarmuid_O_Brie

Does that mean that they can only be displayed in the same order as they are entered, as entries are not necessarily entered in chronological order, ref below sample [fyi, commander = User]?

thanks

Steve

Not at all. They’d be order as you choose:

Diarmuid_O_Brie

Thanks, however the UX definition for my bookings interface, on which (I understand) the inline view is based, is a calendar, which does not have such as option, that I can see

Steve

In UX >> Views, do you have a view for the Bookings data set in the ref position that isn’t a form or detail view?

Diarmuid_O_Brie

I’m using a calendar view, in order to see all bookings together. I’ve now selected the ref position, but it doesn’t seem to make much difference

Steve

You’ll need to create a slice in the Bookings table, configure the column to refer to that slice, then configure the ref-position inline table view for the slice.

Diarmuid_O_Brie

thank you Steve for your continued patience with me on this matter. I have played around a lot, and found that the only way I could get the inline view to adopt the same display settings as those which are defined in the relevant UX definition is by making another table, assigning a new UX [using card & Ref], and pointing the cross-ref in the user table to pull the Ref rows from this new table, rather than the original.

I have done this for two such cross-ref tables in my app, thus allowing the user to view future bookings, plus past [completed] entries too, from the user page.

Whilst this works well, it’s a little bit of a pain, as one of my original tables has lots of VC’s, which I need to duplicate and maintain in the second table just to have a different UX. I tried to do it via a Slice [i.e. assign a slice to the UX that pulls the data definition from the first table with the VCs already catered for, instead of creating a 2nd table and duplicating the VCs], but this did not work. Have I missed something or this correct?

many thanks

Steve

I’m impressed with your industriousness! But there is a much easier way to do it. Create a slice on the original Bookings table. Then, in the configuration of the Ref column that refers to the Bookings table, change the Source table to refer to the slice of Bookings rather than to the Bookings table itself:

After saving that set of changes, go to UX >> Views and locate the inline table view for the slice and configure it to include the columns you want displayed, the desired row sorting order, and whatever else you want in the inline table: