Filter records depending on layout

[Mark V]

Hello.

I am working on a filemaker database for a small sales team that keeps track of contacts, companies, projects, and leads. Most projects start out as a lead. In other words, some leads turn into projects, but most don't. My client wants to keep track of projects and leads on separate layouts.

Since projects and leads share 90% of their fields in common, and I need to be able to easily convert a lead into a project, it makes sense to have one table called "Projects" and a boolean field to specify whether or not it is a lead. Then, once a lead graduates to a project, all that needs to happen is a change of that boolean value.

I have a Projects layout and I have a Leads layout. What I want is for the Projects layout to only display records that are projects, and the Leads layout to only display records that are leads. This would be determined by examining a boolean field called 'isLead' in the Projects table.

My thought was to create a global field in the Projects table that is always '0', then create two table occurrences of Projects that relate to the 'isLead' field. One TO would be related as Not Equal to zero, and the other would be related as Equal to zero. I was hoping that layouts that show records from those TOs would only display the related records, but apparently that is not the case. Both layouts are showing all the records.

Can anyone point me in the right direction?

Thanks!

[bruceR]

"Can anyone point me in the right direction?"

Yes.

Change your data structure.

Use separate tables for Leads and Projects. This explicitly addresses the problem you mentioned. It also allows you to look at the data from the original lead and compare it to the resulting project, where the customer may have changed his mind on certain details or enlarged the scope of the project.

[comment]

I was hoping that layouts that show records from those TOs would only display the related records, but apparently that is not the case.

Any layout is capable of showing any found set from the base table. However, you could trigger a script OnLayoutLoad to find the appropriate records.

Or use portal/s to show the filtered types.

Edited January 17, 2010 by Guest

[Mark V]

Use separate tables for Leads and Projects.

My original plan was to have separate tables. Whenever a lead turned into a project, my plan was to have a script that created a new record in Projects and copied all the info from the Lead record to the new Project record. Since projects and leads are also related to multiple other join tables, I really think the one-table method is best.

However, you could trigger a script OnLayoutLoad to find the appropriate records.

Yeah. Using OnLayoutLoad to trigger a find script seems to be my best bet in this case. I have that much working right now. Going to the Projects layout shows the 'projects' found set... ditto for leads. Theres just a few things I can't figure out to make this work the way I want.

1) If I press "Show All" on either the projects or lead layout, all the records show. Is there any way to make "Show All" only show results from the base found set?

2) If I do a find on one of the two layouts, it shows results from both projects and leads. Again, is there any way to always maintain the base found set?

[dayzrespite]

Basically you would have to simulate a Show All button. Instead of doing a general Show All, this button will be used to find all the records you want to be shown, Leads or Projects, depending on the layout you are on.

As for the Find, script it too. Make it so that you search on either Leads or Projects and also the criteria your user wants.

If you have your default menu, perhaps you can customize it and provide the Find and Show all scripts instead of the regular filemaker options. (Just a thought)

This way you will simulate your Leads or Projects. Remember to think of the possible ways a user can get out of your base found set, and prevent it.

Edited January 17, 2010 by Guest

[David Jondreau]

1) You can have three tables. Leads, Projects, and a parent table that has the common fields.

2) Use the OnModeEntry script trigger to limit found sets on Finds. A simple script with Constrain Found Set[] will do.

[bruceR]

My original plan was to have separate tables. Whenever a lead turned into a project, my plan was to have a script that created a new record in Projects and copied all the info from the Lead record to the new Project record. Since projects and leads are also related to multiple other join tables, I really think the one-table method is best.

Get over it. Use separate tables. The method described b

Yeah. Using OnLayoutLoad to trigger a find script seems to be my best bet in this case. I have that much working right now. Going to the Projects layout shows the 'projects' found set... ditto for leads. Theres just a few things I can't figure out to make this work the way I want.

1) If I press "Show All" on either the projects or lead layout, all the records show. Is there any way to make "Show All" only show results from the base found set?

2) If I do a find on one of the two layouts, it shows results from both projects and leads. Again, is there any way to always maintain the base found set?

Get over it. Use separate tables. The method described by DJ could solve your problem. Or a variation on that approach, adding only a Projects table, which has very few fields. I assume everything starts as a lead; some subset converts to project. So Leads has ID field LeadID. New Projects table has, for example, only two fields, LeadID and DateApproved, and is related to table Leads by LeadID.

[Mark V]

Thanks everyone for your guidance.

After more experimenting/research, I've concluded that using 3 tables is the best solution. I now have a table called Projects with 1 field call ProjectID, a table called Leads with 1 field called LeadID, and a table called Parent with ParentID, ProjectID, LeadID, and a whole bunch of other fields. Projects and Leads are related to the Parent table via their respective IDs. The Projects layout shows records from the Projects table... Leads layout from Leads. Everything is working exactly as expected.

The only thing i'm having trouble with at this point is how to turn a lead into a project. I want a button on the Lead layout to do this. I would imagine this button would run a script. I know what steps have to happen, I just don't know how to accomplish them.

Basically, and correct me if i'm wrong, I need to get the ParentID that the lead record is related to. Then create a new record in Projects. Then, in the original parent record, remove the old LeadID and insert the new ProjectID. Finally, delete the old lead record.

So far my script can get the Lead's related ParentID and create a new record in Projects. Im having trouble figuring out how to do the rest.

[bruceR]

You never change any IDs. The Lead and Project tables relate to Parent by a foreign key set = Parent ID. In the new Project record, you enter the ID of the Parent record as the foreign key.

[Mark V]

You never change any IDs. The Lead and Project tables relate to Parent by a foreign key set = Parent ID. In the new Project record, you enter the ID of the Parent record as the foreign key.

Ahha! Can't believe I missed that one. Looks like everything is working properly now. My new script to convert a lead to a project basically gets the ParentID, deletes the lead record, makes a new project record and sets the ParentID.

Thanks again to everyone for your help. And thanks Bruce for nudging me over to the "Three Table" solution!