Show filtered date columns in related table

[Ksenia1]

Hello,

I am working on an App to schedule slots with managers in certain locations. I want only slots to be selectable or shown, which are more than 14 days in the future from today's point of view. Working with many-to-many relationships, I have a helping table (LocationManager) to combine managers with locations. And another one (Reservation) to combine the LocationManager helping table with the slots.

The procedure to schedule a slot is as follows: the user chooses a location

-> in the location_detail view they choose the manager

-> and in the manager_detail view they choose the slots through the displayed related Reservation table.

Now, to filter the dates to be bigger than 14 days from today, I created a slice for the table slot with the condition:

TODAY() < ([Slot] - 14)

And in table Reservations I chose this slice as my source table

Though, dates smaller than 14 days from today are still shown. I am running out of ideas on how to solve it. Can anyone help?

Furthermore, I changed the locale in my sheet from UK to Germany and did the same with the localization in the tables in AppSheet. It made the changes to the DD.MM.YYYY format everywhere but in the Reservation table (still being displayed as MM/DD/YYYY). Is there another step missing?

[Suvrutt_Gurjar]

---

@Ksenia1 wrote:

Though, dates smaller than 14 days from today are still shown. I am running out of ideas on how to solve it. Can anyone help?

---

You may want to double check slice results. Testing shows that an exactly identical expression filters out the dates less than 14 days from today.  ( The date format in the test app is mm/dd/yyyy)

---

@Ksenia1 wrote:

It made the changes to the DD.MM.YYYY format everywhere but in the Reservation table (still being displayed as MM/DD/YYYY). Is there another step missing?

---

You may want to take a look at the below mentioned article and the section therein.

Supported locales - AppSheet Help

@Fabian_Weller  may have some insights to share on date /time display in Germany based format.

[Ksenia1]

Thanks @Suvrutt_Gurjar my slice works fine:

But I am stuck at the next step of making these dates show also in my related view. The difference in the date format seems not to be the issue, I changed the locale in my sheet and App to US but it still doesn't use my slice in the related reservations view. So I guess I am missing something.

[Suvrutt_Gurjar]

Oh okay,  got it.  Please check the reverse reference column expression Slot <14 slice  in the parent  table has an expression something like 

REF_ROWS("Slot < 14 ", "Parent Table Key")

Also , please check in that column's setting the Referenced Table Name is Slot < 14 (Slice)

Edit: Made some changes in the description.

[Ksenia1]

Thanks again, we are getting closer and yes, the correct time slots are shown now BUT I still want to stay in the Reservations table to add a new row in there and not to go to the slots directly. So, the Reservations table shall show my filtered dates which are bookable.

[Suvrutt_Gurjar]

---

---

@Ksenia1 wrote:

BUT I still want to stay in the Reservations table to add a new row in there and not to go to the slots directly. So, the Reservations table shall show my filtered dates which are bookable.

---

---

Could you elaborate this  a bit more? 

[Ksenia1]

So, the point is for the user to select the location, then the manager and then the time slot (filtered by only those dates, which are at least 14 days in future from today) and finally to confirm these choices/ add a record to Bookings table. In my case, the confirmation happens by navigating to the Reservation_Detail view after choosing the slot and adding a record to related bookings. But if there's a better option, I am happy to try.

The Reservation table has information about the LocationManagerID and SlotID

And my Booking table saves the picked ReservationID and useremail

I hope this helps.

[Suvrutt_Gurjar]

Thank you. Maybe more details will be required, but based on understanding so far, are you able to add  the expression Slot < 14[SlotID] in the valid_if of the [SlotID] in the reservations table  so that the reservation form presents the available slots as drop down option?

One more suggestion will be not to include special characters such as "<", "&" ,"*" and others in the naming of slices or tables. The table names slice names are used in expressions and special characters could be construed as relational or mathematical operators by the app editor. I am unsure if Slot < 14[SlotID] will parse.

[Ksenia1]

Nice idea. I changed the name of my slice and did as suggested but it doesn't filter out the dates. 

[Suvrutt_Gurjar]

Could you update what is this screenshot , meaning related reservations in which parent table's detail view?

[Ksenia1]

It's in the LocationManager_Detail view (after having picked the location and manager)

[Suvrutt_Gurjar · Accepted solution]

Based on understanding, you could try the following.  Is the understanding correct that you wish to show  in the related reservations, only slots that are more than 14 days in future?

If so, please create a slice  called say "14 DaysPlus Reservations" on the "Reservations" table with an expression something like [SlotID].[Slot]>TODAY()+14

Please create a reverse reference column in the LocationManager Table such as 

REF_ROWS("14 DaysPlus Reservations", 'LocationManager Table  Key")

Please use this reverse reference column to show the related reservations greater than 14 days in the LocationManager_Detail view.

[Ksenia1]

Thank you @Suvrutt_Gurjar, even though this slice works (checked it in a separate view), and the dates got filtered out. But it remains unchanged in the LocationManager_Detail view

[Suvrutt_Gurjar]

Are you able to share the column list of LocationManager table and highlight the suggested reverse reference column and its expression screenshot.

[Ksenia1]

This is the column list of table LocationManager

My App Formula for the virtual column

My slice

[Suvrutt_Gurjar]

Thank you. Hope you have included slice name in the Reverse reference column [Related Reservations]' s " Referenced table name " setting in the LocationManagers Table.

Edit: Made some changes to the description.

[Ksenia1]

Sorry @Suvrutt_Gurjar , I didn't get that. Where shall I include the slice name?

[Suvrutt_Gurjar]

In the [Related Reservations] column that is in the LocationManager table. Please fill in the slice name in the "Referenced table name" setting of that column.

[Ksenia1]

Ah got it! 

And YES! that was the last missing piece. Thank you so so much @Suvrutt_Gurjar for fighting through this with me :')

[Suvrutt_Gurjar]

Thank you for the update. Good to know the functionality now works as desired.