Filtered Pop Up Issue

[Ron Cates]

Hi All,

I am looking for some suggestions on how to work around the following:

I have a table and layout for Jobs. Each job is assigned to certain employees, which is done with a popup menu in a portal that shows records from a join table between the employees table and jobs table. The portal is set to allow creation of records. Assigning an employee is a simple matter of clicking the field and selecting the name from the popup which creates a record in the join table. The popup is based on the employee ID and employee name, showing only the name. So it shows the employee names but populates the employee ID as a foriegn key. Now the old familiar issue. I have filtered the value list to only show active employees. As a result, the jobs that were assigned to employees who are no longer with us ( and therefore have been filtered out of the value list ) show only the employee Id rather than the name.

I am aware that allowing creation of records in a portal is not recommended by most of the highly respected developers here on this board. And of course using an alternative method to create the records would allow me to show the name field in the portal but I am struggling with finding a method that will be as quick and easy for the user ( Popup, select name, done! ).

Thanks in advance for any suggestions you may have.

Ron

[comment]

You can show the name by placing the name field from Employees in the portal. Make it non-enterable to prevent accidental modification. If you place it over the pop-up it will look the same as what you have now.

[Ron Cates]

Thanks Comment. That seems easy enough. Put the name field on top of the popup field, set the background color and it hides the other field. Set it to non-enterable and when it's clicked on it activates the pup up behind it. Very nice.

Now I just need a technique to conditionally format the field background so it alternates the way I have the portal row colors alternating. I have not worked with conditional formatting thus far so any help on this would be appreciated.

Thanks

Ron

[comment]

This is not as simple as it could be: first, define an unstored calculation field in the join table (result is Number) =

Get (RecordNumber)

then set the formatting condition to:

not Mod ( JoinTable::cRecordNumber ; 2 )

[Ron Cates]

Thanks Comment. Once again you are the bomb.

There is one small remaining issue. Since I am allowing creation of records throught the portal, there is the matter of that extra row which shows my delete button and now also shows the empty name field where the conditional formatting is not effective. Obviously since this row doesn't actually represent a record the calc field can't give a record number and thus the conditional formatting can't work. If you have any ideas on how to resolve it, that would be great. But I won't cry if it has to remain as is.

Thanks Again

Ron

[comment]

Hm, a tough one... How about:

JoinTable::SerialID and not Mod ( JoinTable::cRecordNumber ; 2 )

or

not JoinTable::SerialID and Mod ( Count ( JoinTable::SerialID ) ; 2 )

[Ron Cates]

Sorry to say, no luck with either of those.

[comment]

Sorry if that wasn't clear: it's not "either of those", it's a single condition using an or operator:

JoinTable::SerialID and not Mod ( JoinTable::cRecordNumber ; 2 )

or

not JoinTable::SerialID and Mod ( Count ( JoinTable::SerialID ) ; 2 ) 

[Ron Cates]

Comment, you never cease to amaze me. That ties it up quite nicely.

Thanks so much as always

Ron