Customized Search page. Is it possible?

[Bfabes]

Hi,

I am working on a movie and I have been asked to create a DB in FMP to track all of our digital art work. I am very new to FMP.

Each Project has multiple smaller assets. For instance. We are building a lightcycle. That 'Project' is composed of smaller assets tires, handlebars, lights, etc.

When the smaller assets are input into the DB, I have 3 pulldowns to assign them to a particular project.

An example of the Project name is 005_Veh_Lightcycle.

005 = SerialID (range is 1 - 120)

Veh = CategoryID (4 possible categories)

Lightcycle = NameID (# is dependent on category)

With the various combinations there are 100s of project names.

What I need help with is creating a report/search page where someone can search for a specific project and have the report give them all the data on the smaller assets.

How do I create a report that will give the specific info for a single project chosen by the user? Do I use the previous 3 pulldowns to create the search parameters and then pass that to something? Is there a better option?

I am searching in the dark here. Any help or nudge in the right direction would be greatly appreciated.

Thank you.

[*susan*]

Can you outline your table structure? It is hard to envision based on your narrative above. Sure someone can assist if they have a clearer understanding of the problem.

*susan*

[*susan*]

I am going to guess that this is your first-ever database. Is this correct?

*susan*

Edited March 25, 2009 by Guest

[Bfabes]

You would be correct. It's that obvious huh?

[*susan*]

'fraid so.... May I rename some fields, and redefine your value lists to show you some "tricks?" or would that be insulting?

Your ideas are clear; but the implementation will eventually break. And Murphy's Law says, the system will break at the most inconvenient time.

*susan*

[Bfabes]

Please do as you will. I have spent days scouring this message board for answers and it is a great resource but I am lost. It would not be an insult.

I realize this is probably a simple DB to construct if I had time to read my Missing Manual book.

The basic gist is that the Projects share the same name as the assets except the assets have another section in their name to further describe what they are as a component.

The producers want a top down view of the project and it's status. So If they pull up the report by the general name 005_Veh_Lightcycle. they want to see the status of all the assets associated with the Project name.

Thank you for taking the time to look this over.

[aldipalo]

First, I would suggest you do a little reading before getting to deep in to your database design. Poor structure has a way of coming back and kicking you. Go here:

http://www.foundationdbs.com/downloads.html

and read 'White Paper for beginners and database design.' They are 2 well written articles that will help you tremendously.

It is generally considered bad design to use anything other than serial ID's as your key fields in relationships. Whenever you use a name field, of any kind, you stand the risk of someone changing the name on one side of the relationship and as soon as that is done the relationship is broken on ALL related records on the other side.

In general naming conventions you will often see pk_ (Primary Key) and fk_ (Foreign Key) used. e.g.

pk_NotesID -> fk_NotesID

I think once you read the above references you will have a better feel for setting up your relationships which will help you solve your problems. I would also suggest that you look at Kevin Frank's Anchor-Bouy model for relationships.

http://www.kevinfrank.com/demo-files-user-group.html

Don't despair we all started here and we'll be happy to help you get through it all.

hth

[*susan*]

Okay. Here is a revision that might get you started.

Let me talk about naming conventions. In general when I see a g as a field name prefix, I assume that the field is a global field, but none of your gFields were globals. I preface calculations with _c_. There are a variety of conventions to choose from. Pick one that makes sense to you, but consistency will help you work efficiently.

In several tables, you had more than one PRIMARY key. Though I am sure someone here will correct me, I can't think of a time that I have ever needed more than one PRIMARY key. I have prefaced the PRIMARY keys with PK_. Just makes it easier to find. I also renamed the PRIMARY key in PROJECT to PK_ProjectID.

There are also FOREIGN keys in Asset and Note. In both cases, they have been named FK_ProjectID. This is how we will build the relationship between the tables. [You can have many FOREIGN keys per table, unlike PRIMARY.]

In PROJECT we also have FK_SetID. This way we can select the id from SET, and fill in the rest of the dependent fields in Project. You will also see that I have renamed fields so that the names are consistent from table to table. [The less time you spend remember which AssetName you are looking at, the easier maintenance will be. So the assetName that comes from set is unchanged, but the other one has been renamed projectName.]

I created one value list for the PK_SetID to make it easier to select at the PROJECT screen.

Finally, I have no idea what you are trying to do with the ReportLookUp, but I think it might be a dead end.

Please review the field comments and post back with your questions.

Good luck!

*susan*

Edited March 26, 2009 by Guest

[Bfabes]

WOW! I can't thank you enough for this. I will go through it now. I am sure I will have a few questions. Thank you for taking the time to sort me out.

I want to explain two things that will hopefully clarify what I was trying to do.

The pulls downs on the Art Assets page. I thought to use those for the user to establish what Project they are associating the new asset with. The thing is I have been saddled with a naming convention that is rather cumbersome. All our assets are coded like this.

The convention is:

000_Veh_Name

000 - is the serial number assigned.

Veh - is the category of asset

Here is my problem. Each category has it's own set of serial numbers. The naming convention was setup backwards but it's what I was given.

So for the category SET there are 120 names. For VEH there are currently only 32 Names but all are different from SET. That's why I kept the Serial pull down just the actual numbers.

The conditional pull down for Category established what Project Names filled the third pull down. The Project Names are entirely dependent on the Category type.

As for the Look Up page. I am sure it was misguided. What I was trying to do is I wanted to create a page for the producers to go to so they could use the pulldowns to select the appropriate Project they want to see.

I realized that if I just used the same pulldowns from the input page, it was changing the original records in the Project Table. That can't happen. I thought if I recreated the pulldowns and have them populate a new table, that is only used to store the producer's choices and then look up the appropriate records in Projects and Assets that I could avoid changing the original records.

And then Layout #4 would populate with the appropriate info.

So that takes us back to my problem of how do I let the producers select the Project they want to see and have the report fill in the proper info based on the Project name they have selected?

My first attempt populated a pulldown with all the concatenated Project Names but as you can imagine, that pull down became way too long with all the possible combinations.

Thank you again for all of this. I will start going through it now to make sense of it all.

[*susan*]

Ah! Then I need to make one more adjustment to the sample file..... Give me a few moments, or about 10 minutes, to redo the SET table.

*susan*

[ndmcconn]

Hey,

what I found useful when I first began my fmp journey, was fmp DVD's from MacAcademy.

It was like taking a seminar, where I could take the professor home with me. I took notes, and

it saved me a lot of trial and error.

ndmc