Beginner looking for guidance w ER chart (pic inside)

[ldeikis]

Hello.  I've worked my way through a fair chunk of the Missing Manual for FM14 and am trying to create what is a pretty simple solution.  I recognize that I could go a long way up the wrong tree if my my overall schema is poor so I'd love some guidance that I expect will be intuitive to those of you with more experience.  As a "customer", I am essentially trying to track and compare the items within a rental order through its steps (which I think of as versions):   

• Items ordered by us (qty and generic type, ie, "35 widgets, 10 gizmos")
• Paperwork confirmation of the order from the rental house (qty of some generic items ("35 widgets") but also unique SKU's of many items ("Gizmo #9012", "Gizmo #1245", "Gizmo #4567", etc)
• Physical count by hand and w barcode reader of the order as delivered (again, qty of generic items as well as now tracking the SKU's of barcoded items)
• Physical count by hand and w barcode reader of the order when we return it (again, qty of generic items as well as now tracking the SKU's of barcoded items)
• Paperwork confirmation of the order when we return it from the rental house (again, qty of generic items as well as now tracking the SKU's of barcoded items)

Obviously in an ideal world all those things are the same, but in reality there are hiccups.  I have a bunch of filtering and reports and warnings (ie, did we scan the same item twice) that I will need to build, but I need to settle the general schema first.  I am stumbling on how to deal with

1. The concept of the "versions" of the order--should an ORDER be an entity, and then EACH version is an ENTITY beneath it, or should the version simply be an attribute?  I think that's correct (though it's different from what's attached in the diagram)
2. How do I manage the fact that the SKU's aren't introduced into the tracking until the second version (when the rental house sends us their confirmation)?

Thank you for guidance.  I'm hooked on the possibilities this opens compared to how we've been tracking this stuff and want to do it right.

PS--I do not have access to a list of items and their SKU's that the rental house is choosing from when they fill our generic order...  we essentially import those SKU's into our solution from an excel format when we receive the rental house's check-out confirmation.  I fear that allowing equipment to be a standalone entity won't work without separately entering the equipment and SKU info, which I think can be avoided with the schema on the second post (see below)

Luke

 

Edited December 5, 2019 by ldeikis

[ldeikis]

I am also open to suggestions on elsewhere to look to figure this out.  I've read the missing manual bits forward and backwards and still am wrestling.

Alternative schema:

 

[comment]

This is not easy to follow... 

My impression is that that these values:

• quantity ordered;
• quantity confirmed (with optional added SKU?);
• quantity received; 
• quantity returned;
• quantity confirmed as returned

are all attributes of a single line item record. You may have to add line items during the life cycle of an order if, for example, you receive an unordered item. But it still comes down to two, possibly three tables:

Orders -< LineItems >- Equipment?

However this raises the practical question of how to associate the values received from a confirmation or from a physical count with the already existing line items. This is not really a question of structure: it may be convenient to have separate tables for these stages (similar to your 2nd ERD - or perhaps a single table for all types of counts), but either way you need to find a way to link these values, if your purpose is to compare the quantities and mark any discrepancies. Which could be difficult in the absence of an SKU or some other unique identifier that would be common to all stages.

 

Edited December 5, 2019 by comment

[ldeikis]

Sorry if I've done a bad job explaining it. 

If it removes a major barrier, I can eliminate the initial version of the order (the one with no SKU's) from the entire solution, and just start this process with the Confirmed Out version.  That isn't terrible for me, as the person generating the rental house confirmations and the boss submitting the original order won't play nice about using exactly the same language for a given item ("gizmo with ears" vs "earred gizmo") so I have to intervene a fair bit already.  I can just manually double check that.  It isn't clear to me that that solves my issue, though.  And doesn't work around the generic items which have no sku.  

I think I'm just fundamentally having trouble shifting my thinking into a database paradigm.  In excel, this was all accomplished with very lengthy arrays of if;then lookup arrays that looked for the SKU, or if the SKU was entered as "NONE" matched the Equipment Type, and then could generate totals and compare...  but it was getting completely insane and I realized I was using the wrong tool.  I believe FM can do what I need pretty easily if I can just wrap my head around how to structure it...  

Edited December 5, 2019 by ldeikis
clarity

[comment]

As I said, I don't see this as a structural question but a workflow issue. You would face the same problem if comparing two lists manually. If you don't have some sort of a dictionary, then you need to align the lists manually using user's input.