How to create a portal that shows all the children and lets you create records via a relationship

[jjjjp]

I understand that the standard way to show all the records of table in a portal is to use a cartesian self-join, but that trick doesn't seem to let you create new records via the portal. All I could think of was joining a global field called IsTrue in a different table to a regular numeric field, also called IsTrue, in the table Child. Both fields are set to auto-enter 1. This trick sort of works, but it seems like a bit of a hack. Is there a more elegant method?

 

I say the trick sort of works because I can't successfully reproduce one of the features that works fine in other portals. Specifically, I want to bypass the FM-native error messages and use my own. I have configured all of the fields in my Child table to be non-empty, and one of the fields, ChildText, to be unique. I have created a script trigger in the portal layout to execute the script OnCommit on committing. OnCommit turns error checking off and processes any errors itself before returning 0. For some reason the FM-generator error still occurs when I create a new record with a non-unique value for ChildText and try to commit by clicking somewhere in the layout. The same problem doesn't occur if I leave ChildText blank and try to commit.

 

Is there a way to fix this problem of not being able to over-ride the FM-generated error for non-unique records? I would prefer it if there were a more elegant way of constructing the Portal to begin with.

 

I attach a bare-bones example.

DemoShowAllInPortal.fp7.zip

[Wim Decorte]

I understand that the standard way to show all the records of table in a portal is to use a cartesian self-join, but that trick doesn't seem to let you create new records via the portal. All I could think of was joining a global field called IsTrue in a different table to a regular numeric field, also called IsTrue, in the table Child. Both fields are set to auto-enter 1. This trick sort of works, but it seems like a bit of a hack. Is there a more elegant method?

 

 

The obvious one: use a list view instead of a portal.

 OnCommit turns error checking off and processes any errors itself before returning 0. 

 

That's because the validation happens before the commit.  If you want full control then turn off validations on the field definition level and handle the "OnValidate" event and do your checks and custom messages there.

 

Also: if you make OnCommit return 0 (False) then the record will never commit.

[jjjjp]

The obvious one: use a list view instead of a portal.

 

 

Thanks. I'll think about that. I want consistency in the user's experience of entering data, and though I do use list view elsewhere in the database, that's for very different use cases, so I'll try to first stick with a portal here—if I can get it to work.

 

 

That's because the validation happens before the commit.  If you want full control then turn off validations on the field definition level and handle the "OnValidate" event and do your checks and custom messages there.

 

 

I have to admit that I'm a bit confused about how this would work. How would I, for example, check for uniqueness using a script trigger for a field on validation without committing and without relying on the field definitions. Wouldn't I have to try to commit all of the rows of the portal (as well as the parent)?

 

Also: if you make OnCommit return 0 (False) then the record will never commit.

 

I have found elsewhere that if I include a script step to commit inside a script that is triggered in the layout OnRecord Commit, and the commit produces no error, the record does indeed commit even though the script returns zero.

[Wim Decorte]

 

I have to admit that I'm a bit confused about how this would work. How would I, for example, check for uniqueness using a script trigger for a field on validation without committing and without relying on the field definitions. 

 

You can use ExecuteSQL to check if the value already exists.  That can get expensive if you have a lot of records.  But if you have a lot of records your approach of showing those records in a portal is going to be a performance hog no matter what.

From a user experience point of view, what is the benefit of them seeing all records when they want to enter a new one?

[jjjjp]

 

Keep in mind that Filemaker offers a lot of functionality out of the box - along with a certain style of user experience. The more you want to change these, the harder you will have to work at it. If at some point you find yourself rewriting the application to such extent that you're not taking advantage of any or most of its built-in features, then perhaps you need to reconsider your initial choice of Filemaker as the platform for your solution..

I appreciate the functionality that Filemaker offers, and as I have understood FM better, I have tried to move more and more in the direction of incorporating it wherever I may have been needlessly reinventing the wheel. But I continue to prefer overriding the error messages FM produces on validation, preferring a simple cancel button on the layout that users can click at any time to an FM-generated Revert  option, which some users would have trouble understanding. (On the other hand, I very much prefer the Error message that FM produces when users try to modify an already open record.) This is the first time I have had trouble overriding the FM error message, and I can't figure out why I'm having trouble with this portal, which is almost identical to other ones.

[jjjjp]

You can use ExecuteSQL to check if the value already exists.  That can get expensive if you have a lot of records.  But if you have a lot of records your approach of showing those records in a portal is going to be a performance hog no matter what.

The number of records is quite small in this case, but ExecuteSQL might be taking me out of my depth. 

 

From a user experience point of view, what is the benefit of them seeing all records when they want to enter a new one?

 

I think I just came up with a solution, but first let me answer your question. You can skip to the solution if you like.

 

My database has various kinds of basic data—presenters, locations, times, series, topics, etc.—that a user can enter, but every piece of data is a member of a program, and every program belongs to an organization. So organizations (the type of record I am having trouble with) are at the base. (The ultimate purpose of the basic data is to create workshops, for which I use list view, but they don't matter in this discussion.)

 

Users are always in a specific program when they are entering basic new data. When users want to create new data, that data is filtered by the current program: users shouldn't see anything outside of programs. But administrators need to be able to create new programs for the organization to which they belong. I use the same type of portal layout for viewing and listing new programs as I do for other data, and program records are filtered by organization. I want administrators also to be able to view all organizations and create new ones using the same type of layout, but since organizations are at the base, there is no reason at all to filter them.

 

I solved the problem of FM-generated error messages popping up despite the fact that SetErrorCapture was On in the script triggered by OnCommit by trying to figure out what was special about the way organizations are set up. I realized that in data such as presenters, the validation test for uniqueness was based on a calculation rather than on a text field. E.g., I had created a calculation field in the Presenters table called NameUnique as follows: FirstName & "|" & LastName & "|" & GetAsText ( ProgramID ). In the Organization table there had been no need for a calculation. I had simply specified that the text field OrganizationName needed to be unique. To solve the problem, I created a calculation field called OrganizationNameUnique as follows: OrganizationName. The validation is now based entirely on calculations, and the calculations, unlike the text fields, aren't triggering the FM-generated error.

 

I don't know why calculations make a difference, but I will go with them.

 

Thanks for your help. This was a complicated problem to explain and will probably be even harder to follow than it was for me to explain.