How can I make a layout look like this?

[Jarvis]

I've developed a pretty nice database for running my cabinetshop.

Most of the components are in place and running well.

I'm having problems, however, with one report.

I need to develop a document that shows task fields in a column and cabinet identification numbers in a grid. I can generate this report easily in MS Excel but this would be the only component for my cabinetshop that comes from the dark side.

Can anybody tell me how to generate a filemaker layout that looks like this, or is there anybody available to hire for this purpose?

Thanks for looking at my problem,

Jarvis

table.jpg.zip

[Sunshein]

If you put the fields in the header at the top and use a fairly standard table layout, it would be easier than doing it vertically. You could also add names to your identification numbers (lines) or use an "X"or a solid color in the box indicating work accomplished.

Maybe also include a brightly colored box if the project requires a particular task and a different color if it is not an applicable task.

[Jarvis]

Thank you Sunshein for that suggestion.

I considered using a standard table layout but the field names are much easier to read when they present in column format such as my example shows.

I am hoping that I don't have to complete project with an excel spreadsheet format. I do not, however, wish to compromise on how the report is displayed.

Perhaps this could be done with a series of portals?

[Fenton]

Yes, it would likely be done by creating calculation fields, to correspond with each of your regular fields, then using one of the Aggregate functions, with a relationship set up for what range you want to count or sum. But you haven't really told or shown us what this is counting; all records? a subset of records?

The generic syntax looks like this:

Sum ( relationship::amount )

or

Count ( relationship::amount )

A Cartesian relationship (the X operator) would be "all records". These would be unstored calculation fields, likely the same for either all records, or many records. But that's as far as we can go without know what range of records they're summarizing.

Is it Box Type you're summarizing, for each of the fields? How many different types? Only 4? Possibly more? You might want to create a separate little table to set up the types, so as not to clutter your main file with this. It is a fairly "portable" set up.

It would be best if you could upload a stripped down file of your data, if that was possible. so we'd have something to work with. Just this part of it is all we'd need. It's easy to show than tell.

[Andy Robo]

Can we assume that one column in your image represents one record?

If so, portals... I would have thought so... horizontal portal records from a self-join relationship - then set them out horizontally using the portal dialogue box to define which records are shown... bit of fluffing around to set up, but if its main purpose is simply a report layout would that not work?

[David Jondreau]

I think it's much simpler. Each of the Box Numbers is the same at the top of the report as next to the Box Type. Presumably, an "x" would suffice where the digits are in the body of the report.

It depends. Is each Box Type a separate record with fields for Box Number or is each Box Type and Box Number combo a separate record (probably should be). I think the latter as a join table between Box Number and Box Type would allow a pretty easy report.

[Jarvis]

Here's what I am trying to get done:

I have a library that contains different types of cabinets.

We could produce, for example, a drawer bank or a sink base or a lazy susan cabinet.

The field names represent the universe of possible activities necessary to produce each type of cabinet. Sink bases, for example, require building doors but do not require building drawer boxes. All cabinets require box cut out.

All cabinets have a BOX NUMBER for identification purpose.

All cabinets have a BOX TYPE

Depending on the BOX TYPE the individual fields are populated with the BOX NUMBER (if the task is necessary) or the field remains empy to indicate that this activity is not germane to this BOX TYPE.

[color:red]The goal is to produce kind of a bingo board of activities that must be accomplished. When all the squares are yellowed out the job is complete. This chart gives us a graphical representation of how much work remains and specifically which tasks remain for which cabinets.

I'll try to muddle my way through a horizontal portal and post it back here to see if any of you guys can help some more.

Thanks for everything so far,

Jarvis

Edited June 1, 2008 by Guest

[LaRetta]

Hi there, Jarvis!

Well, you can create many relationships; you can figure out how to use repetitions to pull it off (I can't help you there) or, putting together what Andy and Fenton suggested, do it this way.

Note that the portal at the bottom is only so you can see what is in the field (I don't know what you have in your field, hopefully a number (any number). But if it is text, just change the conditional formatting to not IsEmpty ( Self ) instead of just Self. Note that the fields are based upon your cartesian related (self join) and that each portal is displaying a different row.

In truth, I think I would have had each cabinet item as a record, using a Type field. Anyway, I think repetitions might be easier overall than portals. YOu will be limited to the number of records to either the number of columns you have created whether you go repetitions or portals.

You can add or delete a 1 from the portal below to see the display above change (any number will work). Is this getting you closer to your vision? I only did the conditional format on the first two rows because it's time consuming.

UPDATE: I got over my lazy spell and finished rows 3 and 4. :shocked:

Cabinets.zip

Edited June 1, 2008 by Guest

[LaRetta]

I have a silly question though ... how will you know whether there isn't a number because it isn't needed for that particular record number or whether it IS needed but the work just isn't done on it yet?

[Jarvis]

I can see now why I'm a cabinetmaker and not a developer.

This is the first time I have ever paid any attention to self-join relationships and this is going take me a bit to get my head around.

The thing that got me launched onto this idea a couple of years ago was LaRetta's Field Factory. Conditionally formatting fields have changed the landscape a bit since then but the basic kernel of the idea has not changed.

From the cabinetmaker's perspective, just as a sink cabinet contains a certain number a parts with associative math for those parts it also contains a certain list of procedures with associative work stations for those procedures. This is the basis for how the shop foreman does his scheduling.

The project I am trying to accomplish is to script a list of activities based upon particular types of cabinets. Just getting this list automated is step one. Crossing things off the list is step 2.

If I could somehow attach a "complete / not complete" status to these tasks (then conditionally format them to disappear graphically if complete) I would have the bingo board I'm looking for.

Anyway, thanks for all your help.

There's a free kitchen in it for whoever can make it happen.

Jarvis

[Fenton]

OK, here is a relational method. It may take a little while to see why I created so many tables. But since I had no place to put things I had to do something. I did not create separate fields for each different task. I created a table for them, with their names as separate records. I used their ID everywhere else. This is a fully relational model, which offers some advantages over "fixed" fields for each; it could be extended more easily, to add things like hours, prices, etc.. In any case it's how I'd do it, unless forcefully coerced otherwise -].

I am only counting Job-Tasks if they are not "Done". Otherwise you would need something to say which to show and which not to show; you certainly would not want to count everything from all time.

But it would be possible to show a count of all, then a count of Done. I did that at first, but the "all" would still require a filter, and I didn't want to get into that.

I only did 4 "Box Types," but the rest would be an extension of the same method. Hopefully you have a limit to the number of box types.

[P.S. The counts are on the REF layout. That table is just a shell, to give you a place to count from. You might want to do other things with it later, so best to keep it out of the regular tables.

The "ref_" tables are what I call "reference" tables, where you enter the combos of "tasks" for each job. In my relational method, a particular "box type" only shows the tasks which belong to it. You only show all the Tasks when you're doing a spreadsheet-like counting thing; which is not a data entry table.

There is a little script "Create Tasks", on the job_Box layout, that loops thru the preassigned tasks for a particular type of table, creating records for them for a particular job-box. Then you're ready to go.]

Cabinets_fej.fp7.zip

Edited June 1, 2008 by Guest