Better way to display layout containing 42! portals?

[ckai]

This is more of a suggestion of alternatives than any sort of "help" question.

I have a home screen layout which displays when the database is opened and also goes back to after 30 mins of inactivity. It's divided into 7 columns (1 for each day of the week). There are then essentially 3 rows representing AM, PM and Night. Each time of the day is again separated into 2 rows - jobs that have not been allocated to a staff member in one row and the other row containing jobs that have been allocated OR staff that say they're available for that time of day.

So, 7 columns for each day and 6 rows into total with 2 each representing each time of the day.

I hope that's clear.

These portals, since they're just for displaying data are simply filtered based on global variables. We can change what days get displayed with arrow buttons but this rarely occurs.

With all these portals, obviously it's a bit sluggish to load. I've looked into several things I may do to tidy/speed things up but haven't been able to finalise anything how I want.

I was thinking of using a list calculation for maybe each day but again haven't delved into it fully yet, so I don't know if this is the best way to go.

Does anyone else have any ideas as to a better method we could use? As you can imagine, with 42 portals, it can get quite time consuming to apply conditional formatting and such.

I've seen some pretty clever "tricks" some of you use that turn out to be beautifully simplistic so I'm keen to see what others can come up with.

The layout works fine as it is but it's always been too clunky for me.

[David Jondreau]

Two things come to mind:

1) Relationships are faster than filtering for portal display. Filtering is really only good when the set of all related records is manageable ( I think less than 50, but YMMV ). Create 7 new TOs and filter on those portals for the AM/PM stuff.

2) Conditional formatting is faster, in my opinion, than filtered portals. What about using color coding to indicate availability and/or time of day?

[ckai]

The main idea behind all the portals was so it would be easy to see things at a glance. A blank space in the AM slot meant no jobs needing to be filled. But like you said, I could use conditional formatting and custom sort order (available staff up top, working staff at the bottom) . Ideally it would be nice to have things in a clear grid but this is about thinking of something different.

I'll have to see how this could work. Essentially, the information comes from 2 tables, a jobs table (which shows the infilled jobs), and an availability table which shows when staff are available and if they are working. So I'll more than likely need 2 portals for each day.

Gives me some ideas to think out of the box, thanks :laugh2:

[dansmith65]

Do you ever access this database over the internet? Or is it local network only?

How long does this layout take to load?

[ckai]

Not over the net just LAN. It takes about 3 seconds to load coming from another layout. Which seems like a bloody long time when the other layouts pop up straight away. It also takes about that long to 'refresh' the screen if I use a the buttons to change the date. It doesn't really matter how much data ends up showing hence why I think it's the underlying layout structure since it pretty much has to do the same amount of work no matter what.

In the scheme of things, 3 seconds isn't a long time. It would be if it was a layout that we use all the time like the Clients or Staff, but obviously my design is inefficient and I suppose I'm looking into it as a learning tool and a way to see things differently.

That time is on the recently brought iMac as well, so not a clunker :)

[dansmith65]

3 seconds may not sound like a lot of time, but it definitely feels like a lot when you are waiting for a desktop application to load a page.

How many relationships are used for these portals? How many records do just these relationships return? I'm asking because portal filtering has to evaluate every related record, so if you can reduce the number of records the relationship returns, you reduce the work the filter has to do.

Another thought; try copying the layout and remove one thing at a time to try to find out which one is the cause of the largest time delay. So, for example; one test may be to remove all custom formatting, and see if the load time is impacted at all. The next test may be to remove all filtering from portals.

[ckai]

How many relationships are used for these portals? How many records do just these relationships return? I'm asking because portal filtering has to evaluate every related record, so if you can reduce the number of records the relationship returns, you reduce the work the filter has to do.

Another thought; try copying the layout and remove one thing at a time to try to find out which one is the cause of the largest time delay. So, for example; one test may be to remove all custom formatting, and see if the load time is impacted at all. The next test may be to remove all filtering from portals.

That's a very good point. It only has one relationship, well, one for each of the Jobs and Availability table and it's working through about 3800 records. Everything is pretty much done through portal filtering. I'll probably look at doing a simple experiment of limiting the relationship to the 7 required days and see if the speed is affected.

haha I'll try and avoid removing certain things from the layout as a test (i.e. conditional formatting etc) as 42 portals have a LOT of formatting/filter etc It'll be my last plan to see what happens since it'll take some time.

Good idea.

[Ocean West]

another thing you could consider is using an SQL plugin to query the other table data to get the ID's of the records for each target Portal.

this is rather fast. Instead of having a multi predicate relationship just have a global field that is set with the results of the query ( a return separated list ) then base that against the ID's in the portal data.

The query can do many things like show you all data for the monday for the morning excluding/including this or that for the the associate Kevin - etc.

hope that helps

[ckai]

another thing you could consider is using an SQL plugin to query the other table data to get the ID's of the records for each target Portal.

this is rather fast. Instead of having a multi predicate relationship just have a global field that is set with the results of the query ( a return separated list ) then base that against the ID's in the portal data.

The query can do many things like show you all data for the monday for the morning excluding/including this or that for the the associate Kevin - etc.

hope that helps

Another good idea. Reminds me of my Access days and building queries (some of those were pretty impressive). I do like prefer the idea of keeping things as native as possible though. I had a quick play around with determining the cause of the slowness with a copy database. Definitely the filtered portals. So next week I might plan to add a crap load more TO's to my table.

Filtering portals might be easy and quick but they can come around to bite you on the ass :)

[comment]

So next week I might plan to add a crap load more TO's to my table.

Perhaps you could reduce that number by making each row a record.

[ckai]

Perhaps you could reduce that number by making each row a record.

I'm intrigued, this sounds simple but my brain isn't getting it (which is a worry since I'm having an "energy drink" to get it to work).

You couldn't explain this, please? Every time I think I know what you mean, I stump myself.

[comment]

Every time I think I know what you mean, I stump myself.

It often happens to me too...

Anyway, instead of having one relationship for Monday AM and another one for Monday PM, have an AM record and a PM record and one Monday relationship - matching on date AND time range.

[bruceR]

Can you post your file; or a simplified example; or a screenshot?

[ckai]

It often happens to me too...

Anyway, instead of having one relationship for Monday AM and another one for Monday PM, have an AM record and a PM record and one Monday relationship - matching on date AND time range.

mmm, I was thinking along slightly those lines but I may just have a rethink a little more.

Can you post your file; or a simplified example; or a screenshot?

You talking about the layout in question Bruce or my mess that is known as my Relationship Graph? I would actually be embarrassed to post a screen shot of that!

[bruceR]

Then post a simplified example file. I am not sure how well we can help you if we can't see what you're doing or what you are intending.

[ckai]

Here's a screen shot of the Home screen. This is a week we don't have any unfilled shifts so the top row or each "time" of day is blank. The different coloured staff names in the 2nd rows shows whether they are working (grey) or available (blue).

I've been away for a week, hence the late reply, and I'll be looking at starting to tidy a few things up with it this week amongst other things (namely creating relationships and removing filters).

If people think a different layout would be better I have no problems. Like I said early, I'm just looking at different opinions and options to think about.

EDIT: Must click "Attach this file" to attach file :oops2:

[Ocean West]

I had seen a solution where you don't need ANY portals for this - instead ...

ONE repeating field.

When you land on the layout you run a script to get a return separated list of all the data for each item.

put all that data into a return separated list then each repetition of your 'calendar' grid you can add the items. parsing it as needed for each cell.

Then with some formatting you can add tabs in the calculation - and custom font colors etc.

-

[comment]

Here's a screen shot of the Home screen.

I see 6 rows, hence 6 records - and only 7 relationships.

[ckai]

I had seen a solution where you don't need ANY portals for this - instead ...

ONE repeating field.

When you land on the layout you run a script to get a return separated list of all the data for each item.

put all that data into a return separated list then each repetition of your 'calendar' grid you can add the items. parsing it as needed for each cell.

Then with some formatting you can add tabs in the calculation - and custom font colors etc.

-

I've briefly found a write up about that as well which is originally what I was thinking of doing. To be honest, no matter what I end up doing, I may actually do this purely to have a play and to see what time differences are like. I thought it was an interesting approach.

I see 6 rows, hence 6 records - and only 7 relationships.

Each of the top rows (for each time of the day) are from a Shifts table where as the bottom rows are from the Availability table. Of course then for each of those they have relationships going to the Client table and Staff table to get the relavant information. I've used a "centralized" TO before to eliminate a lot of double-ups (based on an example I saw of yours actually) and it works beautifully but I haven't been able to work out something similar for this situation.

But this comes down to speed and creating more relationships are going to speed things up considerably. Or repeating fields.

[comment]

Each of the top rows (for each time of the day) are from a Shifts table where as the bottom rows are from the Availability table.

Sorry, you have lost me there. I thought we were looking at a single record, with 42 portals based on 42 relationships.

[ckai]

Sorry, you have lost me there. I thought we were looking at a single record, with 42 portals based on 42 relationships.

Sorry mate. Each row in the portals is one record. Since all the portals are filtered at the user level I essentially only have 2 main relationships for the whole layout, one to the Shifts table and one to the Availability table (from a Global table that contains the 'base' date - at the bottom of the screen). Pretty much the portals have a filter that look like: AM = 1 and Date = Global Base Date etc for each of the days and time of day.

A record in the Availability table looks like this:

Date, Staff Name, AM, PM, Night, Working, Not Available

With the last 4 fields being a yes/no field.

So in the screen shot for the 26th Feb, the first line for each of those AM, PM, Night time slots with 'Jenny' in them is one record:

26/2/2012, Jenny, 1, 1, 1, 0, 0

Hope that clears it up. It would be easier with a simplified sample file but I doubt I'll have time to put one together.

[ckai]

Update: In case anyone was wondering, just like anything in life, the simple solutions are the best...but often the last to be thought up.

I ended up redesigning this layout based on how it was used etc. Instead of showing 7 days, it only shows 5. I also ended up having a single row at the very top that show unfilled shifts. This was instead of having an 'unfilled shift' row for each time of the day (there was a lot of blank portals with this method). I used a bit more colour-coding of shifts so they were more quickly identifiable. So in total, the new layout has 20 portals. This didn't actually end up making the layout any faster, if it did it was so minimal it wasn't noticeable.

Then I had a brain wave from what had been mentioned, I was using unfiltered data to populate the portals. Every portal was going through every record to find what it needed. Because I was only showing 5 days I filtered the base tables on those 5 days. So now the portals only go through a fraction of the data to get what they want.

It was probably assumed I had already done this and to be honest I have no idea why I didn't do this in the first place. Layout is quick and with doing the rethink of the design first, it made me look at the efficiency of the layout so it wasn't a complete waste.