Hope the long explanation helps.

We produce catalogs for a particular customer that are made up of parts
printed elsewhere and shipped in to us as well as parts we plate, print
and fold here. The customer coordinates this process using their own
oracle database. We update their database daily and are able to extract
data from it as an inflexible csv file. (they are not going to change
their format, develop reports or give us direct access to their database).

My trouble is showing a final assembly job list with an indicator of
what parts go into the job and whether each part is ready for final
assembly or not.

Until now, I've been importing the data into two tables. The first table
just imports the final assembly jobs. The second table imports the sub
assembly jobs, and based on the Class, Last operation and/or status,
calculates a field to indicate that this sub assembly job is ready for
final assembly.

Up til now, that has worked fine for producing a report/list of jobs
ready for final assembly. But now I need more detail. I need to show
"final assembly jobs" that aren't ready, show which sub assembly orders
make up those jobs and whether each sub order is ready or not.

On my "Ready for Final Assembly" layout, I've tried using a portal to
show the sub parts, but I end up with confused data in the columns. I'd
like a column that indicates whether or not there's a duracover part to
this job and whether or not that duracover is ready for final assembly.
What I get so far is a column that indicates that line 2 in the portal
for job 211975 is a handbook and for job 217482 it's a duracover (and no
way that I've figured out yet to show if it's ready or not).

I've been thinking that I should add fields for each type of sub part to
my final assembly table, then while importing data figure out if a sub
part exists and do the "ready for final assembly" calculation - but I
haven't come up with a workable way to accomplish that either.

The data comes in this format (with some fields deleted):
Final Assembly,Sub Assembly,Class,Qty,Last Operation,Next Operation,Status
211975,,P-ASSEMBLY,282,PP,STARTED,Released
211975,235647,P-HANDBOOK,291,,PP,Released
211975,235654,P-HANDBOOK,291,FOLDED,,Complete
211975,235655,P-DURACOV,291,,PP,Released
217482,,P-ASSEMBLY,80,STARTED,COLLATED,Released
217482,217000,P-DURACOV,88,COV RECD,,Complete
217482,218567,P-LAMCOV,88,FINISHED,,Closed
217639,,P-ASSEMBLY,425,STARTED,COLLATED,Released
217639,216359,P-HANDBOOK,437,PLATED,PRINTED,Released
218049,,P-ASSEMBLY,500,COLLATED,FINISHED,Released
218049,218620,P-NAMELOGO,514,PRINTED,,Closed

Thanks in advance for any direction you can offer.

Jim

Jim Dornbos <usenet (AT) home (DOT) com> schrub am Sat, 17 Jul 2010 10:09:57
-0400 in comp.databases.filemaker:

I'm not sure what the problem ist. In the parts table, you do have a
field that says whether the part is ready or not? If so, put that
field into the portal.
--
Christoph Kaufmann
http://clk.ch

Christoph L. Kaufmann wrote:
Ideally I'd like something along the lines of a duracover "column" in
the layout (and really - a column for each part type) - if the column is
empty, that final assembly doesn't have a duracover part. If the column
has an empty checkbox, it has a duracover part that's not complete yet.
If it has a duracover part and that part is ready for assembly, the
column would have checked box there. So - three possible values for that
column - doesn't need to be a checkbox necessarily, that's just what
came to mind as a way to tell them apart.

What I'm getting is a column that tells me either what the first part
for the job is - kind of a jumbled column since the jobs aren't all made
up of the same parts. Or, if I show the field that says they are ready
for assembly, I don't know what kind of sub part it is. Guess I could
show two columns, but maybe I need to go back and generate another piece
of data that combines those two fields into one value - because that's
what I really want to show.

Thanks,
Jim

Jim Dornbos <usenet (AT) home (DOT) com> schrub am Tue, 20 Jul 2010 22:13:50
-0400 in comp.databases.filemaker:

Create a two calculated fields for each part type. One field has a
constant text value with the part name. You use that for the left side
of the relation to the parts table and match it to the part name
there.

In the second field you ask wheter or not there are child records for
that particular relation. Use the count function, that will look like
this, assume the relation is called PartDuracover and there is a field
called _keyField in the child table that is never ever empty.

count ( PartDuracover::_keyField ) > 0

You can also use the field with the part's name, because in that case
(i.e. if there is a match to count, there must be the value
"Duracover" in it).

You'll need a second calc field in which you ask for the status.
Result is "Complete" if it's complete, otherwise the result is ""
(i.e. empty). Create a value list with nothing but the word complete
in it and format your calc field to show checkboxes with that value
list.

You have a relation that matches all the parts. If you use this one
and show just one related field, you always get the first child
record.
--
Christoph Kaufmann
http://clk.ch

Christoph L. Kaufmann wrote:

Thanks for your help, Chris. I'm locked in training for the next couple
days, but will give it a try when I get a chance this weekend.

Thanks,
Jim