Another Print from Muliple Tables Question...

[JonAgnew]

Hey, guys. I apologize in advance for this. I know its been covered before, I just can't seem to find a solution that works.

Here's a quick rundown. My company books video production jobs. For these jobs, we provide equipment and crew. Some of the equipment is owned, some is rented. One job can have multiple rental items from multiple vendors. Equipment has a category (ie: camera, lighting, audio, etc.) Crew members have a job title assigned (eg: director, cinematographer, Sound) I'm having trouble figuring out how to print all of this information.

My table structure goes like this:

JOBS > LINES_GEAR (what we own) > GEAR

JOBS > LINES_RENTALS > RENTAL_GEAR ; also from LINES_RENTALS > VENDORS

JOBS > LINES_CREW > CREW

I need to print an Equipment Sheet for each job that sorts in the following order:

Job Number

Gear

-Our Gear

-Gear Category

-Rental Gear

-Vendor

-Rental Gear Category

Crew

-Job Title

For the life of me, I cannot figure out how this would work. Any suggestions would be most welcome.

[comment]

Why don't you combine the two tables LINES_GEAR and LINES_RENTALS into one?

I have a feeling I already said something along that line...

... ah, so I did:

you should make an effort to keep both types in the same table - even at the expense of having more fields than are required by any one type.

http://fmforums.com/forum/topic/82461-multiple-line-item-tables/page__view__findpost__p__382310

[JonAgnew]

*sigh* So you did indeed. That's what I get for not listening.

In the earlier thread, my question had been about the gear only, rentals vs owned...Would you recommend merging the LINES_CREW table as well? So, I'd only have one Line Item table that covers all gear and crew?

[comment]

Probably not. But that's instinct speaking; properly, one should look at the functionality of the two tables.

[JonAgnew]

Ok. So, with the two equipment lines tables merged, how would I go about getting the crew data to the print layout?

[comment]

Oops, I completely forgot why you posted in the first place...

One option is to print a portal to one of the tables - preferably the one that normally has less records (I am guessing that would be LINES_CREW). Or use a calculation field in the JOBS table to list the related crew.

[JonAgnew]

Sorry. Having trouble wrapping my head around this. How would a calculation like that work?

[comment]

What fields do you have in the LINES_CREW table?

[JonAgnew]

In LINES_CREW I have the following:

kf_crewID

kf_jobsID

jobTitle

[comment]

If you add an unstored calculation field cLabel (result is Text) =

jobTitle & Char ( 9 ) & CREW::FullName

then another calculation field in the JOBS table =

List ( LINES_CREW::cLabel )

will produce a nice little table for you.

[JonAgnew]

Cool trick. I can now add List() to my arsenal.

A couple of quick follow up questions. It looks like Char (9) is the unicode equivalent of the tab key?

Because the job titles all have different character lengths, nothing was aligning in a readable way. So, I made one set of calc fields for the job title, and one set for the full name. That helped me to quickly sort out the alignment problem. Just out of sheer curiosity, is there a more elegant solution to that problem?

Thanks for all your help. I bow to the master. :ninja:

[comment]

Yes, Char ( 9 ) returns a tab. If you format the field (using Inspector > Appearance > Tabs > Tab Position) to have a single tab at a position large enough to accommodate the longest job title, it will all fall in place.

[JonAgnew]

Thanks. That worked perfectly.