Generate join table by script

[mitchcohen]

I have a relational database (currently FP6 but could move it to 7) that needs a join table that needs to be created dynamically (by a script etc).

The purpose of the database is to track items that go into boxes, and boxes to be shipped to each office. Some boxes contain multiple items. Some items are split into multiple boxes.

Three main tables: Offices, Items, Boxes. Two join tables (office-item and item-box) since both relationships are many-to-many. I have all of that working fine, with an office record showing what items are going to that office.

I need to generate a shipment page, showing an office and what BOXES are going to that office. FP6 has no way to pull this at all. FP7 can, but pulls in too much data - listing the same box multiple times if it contains many items. So that doesn't work. The only way I can see this working is to have another join table, office-box.

The data this needs to contain already exists in the other tables, but I haven't done enough FP scripting to see how to put it together. What isn't obvious to me is how to parse through one table, search a second table, and insert results into a third (this new join table).

The code outline would be something like:

Empty the offices-boxes table

For each office record

  For each item going to office

    If the box is already accounted for, skip it or increase qty as needed    

      if the box isn't accounted for:    

        Insert new blank record into offices-boxes

        Set office-box::officename to office::officename

        Set office-box::itemname to office-item::itemname

        Set the box qty as needed

      end if

    end if

  next item

next office

I could do this easily in 4D or PHP/MySQL, but it isn't obvious to me how to reference data in different tables within one script, or how to specify which table you are referencing.

Any clues, or even completely different suggestions for accomplishing this, are greatly appreciated.

Thanks!

[Ender]

Since your goal is to track shipments of Boxes to Offices, it makes more sense to have a join table of Office-Box than to have a join table Office-Item. I guess I don't see the need for the Office-Item table. See attachment.

This structure would solve your packing list problem, and you can still see all the Items that have ever been shipped to an office. There will be duplicate lines for the same item, but this doesn't seem like a problem to me.

[mitchcohen]

Ender - I haven't looked at your file yet (thanks in advance - I'll take a look in the morning). But the goal is to track both items and boxes. And items don't equal boxes - some large items ship split between boxes (for assembly at the office), and some boxes contain multiple small items. Items are what are ordered for the office; boxes are a result of the items ordered.

What we already have is a setup similar to a POS/invoice system; an office record that contains (through the office-item join table) the items ordered that office. What I'm trying to generate is the same kind of layout, but showing the boxes to be shipped to that office (2 of box AA, 41 of box BB, etc.).

Thanks.

[mitchcohen]

I'd forgotten that we can use AppleScript to automate Filemaker. Since this is an all-Mac office, I've written some AppleScript code to do what's needed. It's pretty straightforward - picks the first office, looks up the items, figures out the boxes and adds a branch-box record, goes to the next item, goes to the next office. Voila - instant join table, and all in FMP6 (a customer limitation).