Filtering of ID Field

[Dana G]

Disclaimer: My company built their database on "first name" & "last name" relationships as well as non-unique project numbers. I am trying to fix it and keep it as close to what they are used to as possible.

That said...

I need to recreate their Transmittals so they can ship stuff out. I have a simple one to many relationship. One Project can have many Transmittals. I have a foreign key <_kf_ProjectID> on the layout.

This field is tied to a value list that displays all of the projects. This all works. How can I get it to filter further to show only the Acitve projects?

After that I'd like to know if it can be pared down further to filter as the project name is typed in.

I'd like to do all of this in the cleanest way possible.

Thanks.

[bcooney]

One approach is to create a field in Projects, __kP_ProjectActiveID_c which = the __kP_ProjectID if the flag_StatusActive=1.

Then, create a value list using __kP_ProjectActiveID_c and ProjectName, showing only the second value. This will give you a list of only active projects (IDs and Names).

Unfortunately, you cannot easily have both a value list that "hides" the ID and support for type ahead. Value lists that hide the ID need to be popup menus, and they don't support type ahead.

If your Project list is too long for a popup, I usually change over to a popup window of Projects in either a list view, or a filtered portal. (See this thread)

[Dana G]

Bcooney,

Thank you for understanding my need. I'm going to try this now.

I think I'd be ok using the pop up window because we are talking about several hundred projects.

I'll take a stab at it and let you know if I have any problems.

[bcooney]

Glad to help. Another idea is to create a new transmittal record, starting from a Project form, and only allow one if the Project is Active.

The basic script is something like:

//On layout Project_form

Set Variable $projID to Project:__kP_ProjectID

If (flag_StatusActive =1)

Go to Layout (Transmittal_form)

New Record

Set Field (Transmittal:_kF_ProjectID, $projID)

Else

Show Dialog, "You cannot create a transmittal for this project because it is no longer active."

End If

[Dana G]

I can't seem to get the value list to show the correct records. It shows all projects. Also the link to the thread for the pop up window isn't working. Do you have the thread id?

[bcooney]

Hi Dana,

The link is working. It is Topic#185517.

The value list must use two fields, the calculated ID that only contains a value if the project is active, and the second field, project name.