Your relationship attempts to link an uninfexed, probably global user id field to an unindeced user id field. That won't work.
Hi, thanks for your reply.
Do you know what my best option would be for filtering the records without a portal?
I need to be able to filter gender/coutrny/completed date etc
Forget my last post. I missed the detail that these are ODBC sourced tables.
How is your layout set up?
is it based on the left hand table occurrence? On which of the other two occurrences is the portal based?
Is the relationship from layout table occurrence to portal table occurrence one to one or one to many?
What you describe would be correct if there is only one related portal record.
My layout is based on the left hand table (users) and the fields are a mixed from both tables.
From the looks of the entire relationship table on the ODBC source, I would say it is one to many, it is extremely complicated!
There are lots of other tables available but these are the only 2 that I need data from (ignore the vouchers table, I was using it for testing). Thats why I have just added these 2 and created a relationship using userid
You haven't answered my questions about your portal.
Assuming that it is based on the second occurrence, a single record could be a correct result. You'd need to go to a layout based on that table and do a find for records of the first table's user id to check.
One thing to check with on a portal that always shows only one record is to make sure that all field objects are part of the portal row. Enter layout mode and move the portal without selecting any objects in the portal row. The objects should move with the portal. If they don't, they aren't part of the portal and that could be the cause of your trouble.
Apologies. The portal is based on the second table (users_join_courses).
Just had a thought. To get a list of users from the users_join_courses table i would have to do the layout based on that and then the portal based on the user table, as many users join 1 course but users can have many courses, if that makes sense?
I will also try your last option just to make sure it isnt that.
"Just had a thought. To get a list of users from the users_join_courses table i would have to do the layout based on that and then the portal based on the user table, as many users join 1 course but users can have many courses, if that makes sense?"
Um, no that doesn't. You have just described a "many to many" relationship. And that is normally set up like this:
With that set up, you can place a portal to either user_course or courses on your users layout to list all the assigned courses for a given user.
By name, it appears that your second table is what I just called user_course and that should then be the basis for your portal and your layout should be based on users.
I now wonder if a portal is needed at all here. You may need to describe what you want to see on the layout given your "mixed together " comments.
I have got the tables you suggested in place.....
I have tried to add a portal as a test using a layout on the users table. As a test I used the user_id, email, First_name and Last_name fields within the portal and filtered it with a course_id of 97 (which I know exists and has users registered to that course but I get a blank portal.
What I am aiming to get is a simple list of user details on different courses that will allow me to filter it by completion date, gender, and other attributes.
Thank you for your help on this.
I don't see a use for having a portal.
Start with a layout based based on the Join table and set it up as list view or table view. Add the fields you want from all three fields to this layout.
To filter the list, perform a find.
I have already created a layout and the list works fine. My issue is not being able to FILTER the list. I know I can search the list but unfortunately it is difficult to filter. For example....
I only want the records for people on course 60 to show up. I achieved this by loading the list using a script which does a find for people on course 60, this works perfect, BUT, once the search is done and the records are loaded, there is nowhere to continue the filtering such as gender, completion date etc etc without doing an omit record search, plus I will be hiding the top bar from view so will need to be able to build the filter function on the layout. Ideally I need to isolate the records for course 60 and then i can find/sort/filter everything, but there is no way of isolating those records. This is why I need a portal because of its filter feature.
There are many ways to perform a find. One way is to set up a row of global fields in your header, one for each field you want to filter on. The global fields can be set up with value lists or just be edit boxes. Script triggers (or a "filter" button) can be used to perform a script that finds all records matching your criteria.
For examples of scripts that use data in global fields as find criteria, see these examples:
Thank you for your help, I will look at the link you sent and hopefully will be able to sort something out that works. I appreciate the time you have taken to help.