multi-criteria relationship to create records when they're unique?

[Tim Falconer]

Hi there again! Here's a little puzzle from a relative novice (me):

I have a parent table and a child table in which I want to create new child records when and ONLY when either <one> OR <both> of TWO fields is unique.

Multi-Criteria relationships don't do that... or is there something I'm not seeing? I've experimented, with the multi-criteria being "Parent Field A=Child Field A" AND "Parent Field B=Child Field B", via a portal, when the first field in the child record is made non-empty and exited, a new record is created, regardless of whether the child field was a match field, and all empty match fields are filled with the data from the matched data in the current parent record. But you knew that...

That does not work for me, because I only want to create a record when either or both of the fields contains a unique value.

...In my case, I only want to create child records where, given match fields A and B in both parent and child tables:

If Parent Field A does not = Child Field A OR Parent Field B does not = Child Field B, OR both, create a record;

If Parent Field A=Child Field A AND Parent Field B=Child Field B, (for any Child record) do NOT create a record.

How would you do this? Script "Find A=A AND B=B", then IF found set=empty, create the record and paste in the match field data for A and B?

If there's a way to do this by setting up the relationship differently, I want to know about it!

(and I really hope there's some really simple solution I've just overlooked...)

(I just experimented with A not equals A and B not equals B in the relationship graph... no blank field in the portal!)

thanks,

tzf

[comment]

I am afraid the question is not clear.

when and ONLY when either <one> OR <both> of TWO fields is unique.

In which table are these fields - and in what context they need to be unique? Perhaps you should explain the real situation behind this requirement.

[Tim Falconer]

Thank you, Comment.

The conditions are restated further down, "If Parent Field A..." etc.

In set terms, call "parent field A = child field A" set X,

and call "parent field B = child field B" set Y,

The set that does not cause a new record is (X intersect Y), and the set that causes a new record is (not(X intersect Y)).

But maybe that's not any clearer at all! In the real world, it's actually pretty simple: We're packing parts into crates.

Parent Field A is the Shipping Order (i.e. the KP, a get(UUID)-generated string) from which the parts are being selected. The parts are on line items, and can be selected by (the Job Order's) Line Item (one part number per line item) and Quantity to pack in a given crate.

Parent Field B is the Crate Number (the shipping manager picks Crate 1 through 10 on any given day... starting with crate 1, they rarely if ever pack 10 crates in a day). The shipping manager can select quantities of parts for crates in whatever order makes sense for packing the crates as he goes down the list of parts for the Job Order.

I want to create separate record for each crate, which itself will get a unique Crate ID (and a unique QR Code).

So for Shipping Order 4321, if Crates 1,2, and 3 have previously been created, and crate 4 has not, when the shipping manager selects parts for crates 1,2,3 and 4, a new record will be created for Crate 4 only. The other parts will be "packed" into the existing Crates 1, 2, and 3, which is to say, the Shipping Order Line Items for Crates 1, 2, and 3 will have their Foreign Key field for Crate filled with the Primary Key for the Crate in which the Line Item is packed.

Finally, after all the parts have been assigned to Crates, packing lists are created, one per Crate, which show the Shipping Order Line Items in each crate, along with various dimensional information (mainly Crate Net Weight), while the Shipping Order itself shows all of the line items in the Shipping Order, (once again with dimensional information such as Total Net Weight) and all of the crates to which the line items have been assigned, from which a Bill of Lading can be created, when necessary.

(and Child Field A will be the Crate's KF_Ship Order, and Child Field B will be the Crate's "Crate Number" for the day's ordinal crate number 1,2, 3, etc.)

I hope that's clear. Lots more detail in the actual solution of course.

thanks

tzf

[comment]

I am afraid it's clear as mud...

Would it be correct to say that starting with:

Orders -< LineItems

someone (the shipping manager) will go over the line items and divide them into crates, so that eventually:

[comment]

Or possibly - to accommodate partial shipments:

Note that multi-criiteria relationships are not required at all.

[Tim Falconer]

Thank you again for helping.

The second ERD is correct, and all of those relationships are shown correctly.

Every time the shipping manager presses the "add to crate" button, it creates a record in ShippedItems.

But it should only create a new record in Crates when needed.

thanks,

tzf

(off-topic, what software do you use to create these neat ERD's?)

[comment]

But it should only create a new record in Crates when needed.

But how do we know when a new crate is needed? It would be easy to have a "New Crate" button for the shipping manager to press when the current crate is filled up - in the shipping manager's opinion, that is.

what software do you use to create these neat ERD's?

OmniGraffle.

[Tim Falconer]

But how do we know when a new crate is needed?

In each line in the portal, there is an entry field for "Crate". It's a drop-down list, consisting of the numbers 1 through 10. The "Add to Crate" script reads the selected value... if the shipping manager has not previously selected "4" for that Shipping Order, then Crate 4 is a new crate.

[comment]

if the shipping manager has not previously selected "4" for that Shipping Order, then Crate 4 is a new crate.

First, I need to say that this type of user interface doesn't appeal to me, for several reasons:

1. It's non standard - I don't think you will find any established application using anything like it;

2. The limit of 10 is entirely arbitrary; in the rare case of a shipment requiring 11 cases or more, all that the user can do is create a new shipment and curse the developer;

3. People make mistakes, and it's very easy to select 5 when only 3 crates exist so far; note also that your value list makes no distinction between existing numbers and the rest.

That said, you could attach a script trigger to the field, and check the selected number against the count of existing crates.

[Tim Falconer]

Thanks for all your help. :)