Help win Portal and how to built a 1 to many relationship

[Nestor]

Good afternoon everybody,

I went clueless how i can tackle the following problem.

I have a table "Art Production" this table consist of

- ap id_K

-job ticket

-item number

-item name

I have a second table "art file" this table consist of

-ap id_F

-art file id_k

-art file number

-art file name

and a 3rd table "ink specs"

-art file id_f

-ink specs (15 repetitions)

here is my problem.

i will create one job ticket (table #1) and this job ticket will have multiple art file numbers, and each art file numbers will have up to 15 ink specs (thats the idea about the repetition field).

at this point i don't know how to tackle this or if my thinking of 3 tables is the right one.

I also need to make sure that if i create a new job ticket number and select and existing art file number all INK SPECS will populate automatically. If anybody has any ideas i'm all ears...

attached is a jpeg of the way i could see this showing up.

1-you have the job ticket upper left

2-up to 5 art file numbers per job ticket(below job ticket in a portal)

3-on the middle of the page you have those art file numbers (up to 5) and inks specs right below.

(portals)

hope this makes sense.

Thanks in advance.

[LaurenKuhlman]

You are on the right track. Ink Specs should be a third table. Your ERD would end up something like this:

As far as getting the layout to work right, that's a little trickier. I would make the context of the layout Art Production. The have a portal list with all the art files. I would also have a portal for ink specs but this is where it gets tricky. I would use the art file portal list as a "picker." When you click on the art file, its related ink specs appear next to it. You would need to shape the UI in such a way that users know what is happening. Also, you would want to apply some conditional formatting so that the "picked" art file turns a different color. This involves adding a global field to the art production table where the "picked" art file primary key gets set via script. Then, adding a table occurrence based on ink specs and drawing a relationship from the global field to the ink specs table. Base the ink specs portal on this new table occurrence.

The functionality to duplicate an art file number and have all the ink specs "follow" is a bit advanced but not impossible. There are lots of ways and many pros/cons but this is essentially how I manage that. When the user clicks a button a script does the following.

1. Set the primary key of the existing art file in a variable.

2. New Record

3. Set the primary key of the new art file in a variable.

4. Goes to a layout with the context of ink specs.

5. Enter Find Mode.

6. Set Field [ Ink Spec::Art File Foreign Key; Existing Art File::Primary Key ]

7. Perform Find

8. Looping Duplicate

9. Replace Field Contents [ Ink Spec::Art File Foreign Key; New Art File::Primary Key ]

Note about user clicking a button: You can override the Duplicate Record functionality using custom menus but that, again, is a bit advanced so, one thing at a time. It also requires FIleMaker Advanced.

Step 3 Note: There are just tons of ways to do this. The best is doing a "popup" with a dropdown but it requires making a layout and a value list. I have seen many people just give the user a custom dialog to enter the job number (make sure the Cancel button exits the script) and then using a ValueCount ( FilterValues ( What They Entered; ValueListItems ( Get ( FileName ) ); "Name of Dynamic Value List with all the Art File IDs ). I guess both require a value list unless you are on a context where you can use List ( ).

Step 7 Note: You should always have error capture after a Perform Find that skips the remaining logic if the find resulted in no results. A Get ( LastError ) is good for this with the note that Get ( LastError ) must be called immediately after Perform Find.

Step 8 Note: See this topic for a how-to of duplicating a found set: http://fmforums.com/forum/showtopic.php?tid/211189/

This is A LOT of information. If you need help, just ask here.

[Nestor]

Thanks Lauren for all the info.

Here is what i have so far.

I created my tables and linked them. I've also created a few portals for both art files and Ink specs.

But now im a little stuck in duplicating my ink specs for a certain art file.

I've attached my database so you can see what i have.

should my next step be creating some type of duplication of ink specs once the same art file number is selected or typed in my art file portal?

My main issue is that 1 job ticket will represent 1-5 art files, but those art files can show up in order job tickets so i need those initial ink specs to follow so i don't have the user do a find and copy of the ink specs. hope that makes sense :

Thanks again for all your help Lauren.

FIlms.zip

[Nestor]

oops here is the file. Last file had some type of security. But if it ask the username is "admin" and no password

FIlms.zip

[comment]

Could you provide some background here? I see what your file does, but I am not sure what it is supposed to do. I am guessing the "Art Production" table is a table of Orders - but the way you have it now, each art file can be sold once only. Is that really the way it works? And what exactly do you want to accomplish with the inks?

[Nestor]

Gladly,

Goal.

-Is to have a database where i can create a job ticket number, and be able to write down up to 5 art file numbers (designs that need to be printed). Those art files will represent the job ticket.

One thing to note is that any new job tickets could have either a new art file or an existing.

With New art file #'s i would enter new Ink specs.

With existing i would want to have those inks specs flow in to the fields under the art file #'s.

So in a way the art file numbers are just components to the job ticket, but those components can be use in new job tickets.

Ink specs are components to the art files, and every art file number will have its own set of INK specs.

Does this makes sense?

I just want to have the corresponding ink specs to a art file number show up under if it happens to be an existing.

Thanks.

[comment]

The ink issue aside for a moment, it sounds just like a basic invoicing solution: a customer orders a number of products from a list of products. If so, you need another table for the order items - see a demo here:

http://fmforums.com/forum/showpost.php?post/309136/

If that's not what you meant, then please clarify further (you haven't really answered my question about selling the same art file more than once).

[Nestor]

ok, here it goes.

we design art work and after the art work is completed we give it a job ticket.

each design CAN consist of up to 5 components. Each component is a art hit. for example

art hit-front t/shirt, art hit-back, art hit-sleeve, art hit-neck, art hit-center chest.

each art hit has thier own ink specs (colors). for example the art hit for a front could have 5 colors that make up the design.

art hits can be re-used, and if they are i need those specific ink specs to follow the newly created job ticket.

so i can have the following

job ticket=abc123

art file numbers

1-8ink123

2-8ink300

3-9mim212

Ink specs for art file #1 (8ink123)

(ALL THESE ARE PANTONE COLORS)

1-186c

2-190d

3-100a

4-200a

5-874c

again each art file will have ink specs.

Now we can create a new design using some of the same art files from previous desings (job tickets)

this is what makes it a little tricky. if i wanted 2 i can grave different art file #'s and pull them in to create a new job ticket. so a new combination of art to create a new design.

The trick is that those art files are existing and i need those ink specs that were linked with that art file to get copied over to the new design(job ticket) but still have them stay in place where they came from.

75% of the time new designs(job tickets) will have NEW art file numbers in which new ink specs will be written down on the fields, but its that 25% that i need to have some type of look up or a way that i can bring in those existing ink specs.

Does this makes sense.

[comment]

i need those ink specs that were linked with that art file to get copied over to the new design(job ticket) but still have them stay in place where they came from.

Why copy? Do the ink specs for an art file ever change?

[Nestor]

No. ink specs for art file abc123 will always be the same.

IF an art file would get a modification then we would just add a version to the art file abc123-b. But would would still keep the parent art file and the original ink specs to that art file.

makes sense.

[Fitch]

As Michael/comment said, you need a "Line Items" table that links your Product (art file) to your Invoice (job ticket).

[comment]

Well, then you just need a link to the original art file record. However, since the same art file can be used in several jobs (IIUC), you have a many-to-many relationship between jobs and art files - and therefore you need a third table to join them.

See if the attached makes it any clearer.

---

Note that I have taken a shortcut here by using a checkbox for the colors; this is simple, but it has some limitations (e.g. there's no place to record the quantity of each color). Strictly speaking, there should be another join table between Components and Colors, since they too are related many-to-many.

Jobs.fp7.zip

[Nestor]

I believe that's what i have in my demo. I just don't know what my next step should be.

I'm having trouble when creating new records and having those ink specs follow the existing art file #.

FIlms.zip

[comment]

I believe that's what i have in my demo.

No, you have a one-to-many relationship between job tickets and art files - which means an art file cannot be reused in another job.

[Nestor]

You are correct.

Thanks for the demo Comment. I feel i just have to add pieces here and there to make this happen.

Thanks for help everybody.

Im sure i will come back tomorrow with more questions.

[Nestor]

Ok, I can't make this thing to work. I feel im getting more confused as i try something different.

I made some modifications to the layout and tables, and im wondering if anybody can point me in the right direction.

in my current db when i create a new record under "Art File #" i put a number lets say (8ink100), then to the right under "1" i will put down 2 ink specs lets say "123" and "234" (ink specs is a field with 16 repetitions).

NOW once i create a new record and i put down 8ink100 for my art file, my ink specs don't populate on the rigth side, and CAN"T figure out how to make this work.

I've looked at the jobs solution/the invoice solution and still can't figure this out.

Help please.

FIlms.zip

[Nestor]

I Did it...

Thanks everybody for your help. I looked at the Jobs solutions and took it step by step, and got it to work. :

FIlms.zip