Display table layout as custom form

[Paarl_Bus]

My school charter bookings are working so parents can book their children. The next step was to create a schedule with passenger names, pickup school and time etc. A simple table layout took care of that, but I would like to have a customized form that is more user-friendly. The table can e.g. group by pickup point but then it’s not sorted by pickup time (The groups, not the rows in the group.) So each row must then display the same pickup time, and the time is not front and center for the users.

I would like to have a form with each pickup point and time as a heading, starting at earliest pickup time (or first pickup sequence) then the passengers’ names etc in sorted rows below. Repeat for the next pickup time or sequence number.

I’m wonder if that’s possible? If not then a list sorted by pickup times would be acceptable, but with the time also displayed prominently somewhere sensible outside of the table.

Furthermore, if it would be possible for the driver to change the sequence if needed - by time if grouped according to time, else by sequence number.
We pick up at a max of 7 places, seldom all 7 together, so not a big job to change the sequence. The driver should just edit the time/seq no. Will the sequence re-sort in the form by itself or can it be done with a button?

And lastly, the driver should have a button to “mark” loading as completed and write the current date/time to each row in the group.

Any ideas?

Edit:

So many issues arose while I was trying out different scenarios. First I noticed the calculated ETA was wrong, I left out a component. I fixed that and now it’s correct but the formatETA column does not update. It is experimental FormatDate type (HH:MM). This formatted time is used in the bus schedule layouts.

The layout consisted of custom containers with a heading (Pickup point, time), and a table with just the passenger name. The layout is filtered on Route (1 or 2) and a date (on/after today). Then the table is filtered on a manual pickup sequence number as well as the route and date as above. A one stage I had the heading displaying the pickup point and time, again filtered by sequence number. I t worked perfect for seq 1. I then duplicated the container, changed all the seq filters to 2, and it refused to display ANYTHING. as long as seq is 1, I can have any number of containers displaying correctly.

Then I tried another layout and lost everything. Now the container shows the correct data and I can duplicate it and change the seq filter for the table but the header stays the same.

Yet another problem I don’t understand is the DateTime columns. I have in my Schools table and my Hostels table each the exact same column, displaying as time only, without seconds, ignoring time zones. In Schools the column has a clock icon, but in Hostels it shows a calendar.

Still trying …

And picked up another bug. Like I said, the table rows display the correct data for that Seq number but no other component does. I added a button with a manual trigger to write the current date/time to the Trips table (for offloading and later picking up too. If I filter the button with a Seq condition other than 1, it disappears. I checked the value of seq number with a text component, a field component and a number entry and they all showed 1, irrespective of what the actual seq number is.

I give up. Anyone has an idea?

[Jeff_Hager]

If you are trying to control visibility of components on a screen, it’s only going to be based on the row that the screen is attached to. If the screen is attached to the seq 1 row, then all component visibility is going to hide or show based on seq 1. A screen can only be connected to a single row and in this case is most likely connected to the seq 1 row. A collection on the other hand can display multiple rows.

Without screenshots, I’m not really sure what you are trying to do, but if you are trying to display different rows of data on the same screen,then maybe you need to use a custom collection, which can be filtered to a specific row if needed. The custom collection will allow you to place components inside of a container that will be repeated for each table row.

[ThinhDinh]

@Paarl_Bus

I would like to have a form with each pickup point and time as a heading, starting at earliest pickup time (or first pickup sequence) then the passengers’ names etc in sorted rows below. Repeat for the next pickup time or sequence number.

Does it have to be a form though? If you store each child as a row in a bookings table, I assume you can use something like a checklist?

@Paarl_Bus

It is experimental FormatDate type (HH:MM). This formatted time is used in the bus schedule layouts.

I would suggest not using this Format Date column if it has shown to be problematic in your case. It has a history of not being reliable across devices.

Use two math formulas to get the hour and the minute, and then use a column, perhaps some JavaScript lines to get it to HH:MM.

[Paarl_Bus]

Thanks for the suggestions. @ThinhDinh I had it originally as a table with a checklist column but I decided against it because 1) it repeats the same pickup time for every filtered row and I prefer it as a heading for that collection, and 2) clicking the checkbox activated the details screen and I need it to write the current time to the Trips table. For writing pickup (and drop-off) times I will use a button at bottom of each collection with a filtered loop workflow; I have tested it and it works.The suggestion for using maths columns for the formatted time is perfect, will do.

Here are some screenshots:

Current WIP layout

@Jeff_Hager, I cannot put a container inside a collection or vice versa. It stays outside.

Currently the manual schedule looks like this:

I am aiming for something similar, top to bottom layout instead of left to right but you get the idea. Pickup point and time as a heading, filtered names for that pickup below. Can preferably be a grid to save space.

So currently my only obstacle is getting the correct place name and time as a heading of sorts inside the collection.

[Jeff_Hager]

@Paarl_Bus

I cannot put a container inside a collection or vice versa. It stays outside.

No, but a custom collection itself is a container as far as laying out components inside each collection item. If you have a table with just your pickup times and a relation to the names for that pickup time, then you could display a collection of names within the custom collection container.

Regarding the following, is that a title component? If you don’t want to see the repeated text, you could just remove the component if you don’t need it or change it to display something else.

[Paarl_Bus]

Ok @Jeff_Hager , I have created custom containers, each with a card collection containing only the passenger name. The correct time and pickup point is displayed in the card collection Group By option. It works perfectly except, again, for the collection that has 4 passengers. It repeats all the contents of the collection for each passenger, irrespective of headings or anything else. 10 names, 9 duplicates.

By chance every pickup point had only one pickup, except no. 3.

You mentioned a table withe pickup times and a relation to the names for that pickup time. How it works is that the Bookings form writes the data to the Trips table that also contains a helluva lot of relation and calculation columns etc, This Schedule pulls its data from Trips and of all that data only the names are directly entered from the Bookings form.

The only time the layout works correctly is if I replace the custom collection with e.g. a card collection, but then I cannot add the button.

Thanks for your time and patience so far. Highly appreciated!

[Jeff_Hager]

That’s the part I was alluding to regarding “if you have a table with just pickup times”. I’ll assume you don’t and everything is in the same table. In that case you’ll need to use the following method to determine the first instance of each pickup time and the filters your custom collection to only show those rows.

Is there a way to filter duplicates from an inline list?

This is what I would do if you wanted to keep everything within glide. First I would recommend having a RowID which will give every row a unique value. This will become important in a little bit. Then I would identify a unique value for each contact, whether it’s a name, phone number, email address, or something else that you know will be in each duplicate row, but is still unique only for that specific person. Let’s assume Name for now. Create a self relation that links the contact’s Name …

[Paarl_Bus]

Hi @Jeff_Hager , you gave me a lot to think about. While I do that, maybe you could explain to me something that has been bothering me since I came across this problem.

I create a custom collection. This collection is based on a table with columns containing data. These columns, and therefore their contents, are available as components to the collection. If I filter the collection, only the filtered contents is available. now then, WHY is a part of the collection (an attribute?) like the Title, not also filtered? This is the cause of the problem, that the collection Title contains the first value as sorted in the Navigation tab. Therefore ALL the containers in that Layout has the same Title, while the components inside it are filtered.

This unfiltered Title now causes endless discussions and increasingly complex calculations. So unnecessary!

Do you think it is possible to do?

Regards

[ThinhDinh]

You can not “filter a title”.

Each card in your Custom Collection ties your data to a row in your table.

When you add a Title to each card, it displays the data from each row, and as you have the same data across rows, that’s why you’re seeing the same thing over and over again.

And also that’s why Jeff suggested you using the workaround to have a “unique” row to represent each of your pickup points.

[Jeff_Hager]

Are you talking about the title that encompasses the entire collection or a title for each item inside the collection? If you are talking about the title that encompasses the entire collection, then that has nothing to do with the content inside the collection. That title is something you fill in manually or retrieve from the single row that the screen is attached to.

I’m guessing your screen as well as the collection on that screen are both sourced from the same table and that’s what probably leading to the confusion. If your screen was sourced from the user table and the collection on that screen was sourced from the pickup table, then it might help clarify the difference because your collection title would only show a available columns from the user table while the items inside the collection would show available columns from the pickup table.

[Paarl_Bus]

Thanks @ThinhDinh , I am still thinking about the unique row. I understand your meaning but it’s all still very new to me. I think my “unique row” is not the name because I checked that and it gives me the first RowID for that person (name) of the entire Trips table. What I need is the first record of the day’s trips for that pickup point - in other words, that collection. I have an idea that it involves using the column with the current row’s date and pickup point (as abortively used in the collection title) and comparing that RowID to the current RowID. Something like that, I’ll play around with it and keep on re-reading Jeff’s suggestion. RowID’s are also still No-man’s-land for me, and I think I can probably replace ParentID and ChildID in the many-to-many helper table with RowID’s. Some studying ahead!

@Jeff_Hager , I am referring to the title for the entire collection, which will be the place and time of each pickup - in this case, the school . The title actually has something to do with the contents of the collection, since it will show where and when the passengers in the content will be picked up. I think I got mislead into thinking the title can show that because you can use the fields/columns for that collection in the title. It therefore followed naturally in my thinking that the column having the current place and time show show up correctly in the title… Didn’t know about connected screen yet and didn’t think to check with another column to see the results.

I had a quick peek at Screen Values in the Docs… eye-opener! Thanks a million so far, I’m slowly getting there.

[ThinhDinh]

@Paarl_Bus

What I need is the first record of the day’s trips for that pickup point - in other words, that collection. I have an idea that it involves using the column with the current row’s date and pickup point

Yes, sounds like you got the logic right here. The bottom line is not a single field must be the “unique” key. It can be multiple conditions together in a query column, then you take the first ID from that query.