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
Solved! Go to Solution.
In Valid If:
(
FILTER(
"user list",
[isActive]
)
+ LIST([_THIS])
- LIST("")
)
In Valid If:
(
FILTER(
"user list",
[isActive]
)
+ LIST([_THIS])
- LIST("")
)
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
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())
)
)
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
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.
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
Not at all. Theyโd be order as you choose:
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
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?
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
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.
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
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:
See also:
thanks Steve, I think Iโve got the hang of this now
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |