records not showing up

[danfoshee]

My head is aching from beating it against the wall; I know this should be easy, but can't find my mistake. I am just now learning relationships and beginning on portals, but obviously I'm missing something.

I've got two databases:

JobTrack, which houses project info

BrandTrack, a new db set up to see very specific info from JobTrack

The reason: I've got a small group of sales and marketing people who want to see the status of this particularly large and encompassing project that the creatives are doing for them. 'No problem,' I thought, 'I'll set up a new file and relate it back to JobTrack, so that the sales and marketing people don't actually get into JobTrack, but can see their relevant data.' And preserving the data integrity is my main concern, as the creatives use this for monthly billback purposes.

In the BrandTrack file, I set up two different relationships to JobTrack (just to see what worked better):

Job# (in BrandTrack) to Job# (in JobTrack)

Brand Code (in BrandTrack) to Brand Code (in JobTrack)

Both fields are populated in the JobTrack file, but are empty in BrandTrack. As a result, no matter which relationship I use, the fields in BrandTrack (which refer back to JobTrack) are also empty.

I know that I can import data into either or both of those fields from JobTrack into BrandTrack, and have the other data on the layout then show up, but that doesn't seem right; what happens when new projects in JobTrack are entered???

I also tried to get the Job# field in BrandTrack to pull the possible job#'s from JobTrack, via a value list, but then I get a message that says each number must be unique. This IS how I set up the JobTrack Job# field, but how do I get around this.

I don' need no more bruises! What am I missing?

[tlsparker]

The relationship key needs to exist in both files as data. One way to create your new related database would be to export the job# field to a new FileMaker file, checking the "summarize by" box in the export dialogue (summarize by job#). I believe you would need to sort the file by job number before doing the export. The new file will contain one record for each job# and meet your criteria for having unique values. Then, create the portals and fields you want your sales and marketing folks to see.

After this initial file setup, you will need to make sure that every time a new job# is created in one file, the job# is auto-entered in the other file. How you do this depends upon your objectives.

Tom

[email protected]

http://www.tgparker.com

[Rumble Seat]

I think I understand: you want the Brand file to simply display select information from the Job file. Easy.

You need to have a unique ID for each record in Jobs. Let's call it Job_ID

You only need 1 field in Brand, also called Job_ID. (It could even be a Global Field).

Create a relationship "Job" matching the two fields

In the Brand file, put the Job_ID field in the layout. Format the field to display a value list, defined as "Values from a Field." Tell it to use all values for Job_ID from the Jobs file! (And tell it to also display the Job name, or date, or even create a calc field in Jobs that combines Client, Job, Date, and have the list display that as the second field.)

Still in Brands, display all the fields you need via the Job relationship, and in the Field Format field, uncheck "Allow entry into Fields" (or setup passwords in Jobs that only allow sales people to read, but not edit...)

Then, in Brands, the users simply select the Job ID from the list, and the data from Jobs will just appear. Hope that helps, and I'd be happy to clarify if this all seems too confusing.

[danfoshee]

Thanks Tom, Rumble Seat (may I call you Rumble?):

Rumble, as said in the original post, I tried doing the job# entry via the field as a value list, but since the Job# is an autoenter, unique text field in the JobTrack db, the Brand db gives me an error message that the job# field must be unique. I can either revert field, or hit okay.

I've since found out that they want to see all of the individual projects at a glance, so I'll have to do a list view as well. So I'm guessing I'll have to do a few scripts to do a search and sort, then display it in list view. I can handle all that, no prob, now that I know why my data wasn't showing up.

I'm wondering if in this case, a lookup might suffice. I prefer related data, but since the file will likely be closed down once the project is done, I'm thinking the data duplication wouldn't hurt here. I could put in a button on both the form and list that would allow them to update the status list (do a re-lookup), and put it in a list view.

Thank you both for pointing me in the right direction. My head is saved!