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...

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:

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 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) 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

Thanks for the quick reply... This seemed very much within the novice corner of FM, but I was ears deep into Excel before I realized I had gone down the wrong road, so... RE subscription, I thought I could buy an individual license that would allow one user/machine to access the software... That would suffice until the point that paying for it isn't my problem. Either I'll be the person supervising the checkins/outs, or my boss or our clients would choose to buy a dedicated machine for the purpose or a license to use it on a broader scale. These jobs are all kind of ad hoc in a few-months-at-a-time way, so an ongoing subscription is too likely to end up on my financial shoulders. Thanks, Luke

Hello. Please excuse the long-winded first post: I apologize for taking the time to ask such a basic question, but this is a platform I will only delve into if it solves this need. By the time I can answer that question, I'll have sunk an awful lot of learning into it, so I figured I'd ask in advance and maybe save the labor. I have no formal programming experience, but grew up in the kind of family that thinks teaching a little kid BASIC is a fun idea and I don't shy away from challenges. I do lighting for television and I need to setup a system to manage rental inventory in/out. I am coming at this from the rentER side, trying to efficiently track and record items received and returned in multiple stages and multiple orders, often overlapping. The workflow is 1.We submit an order to the rental house of items and quantities of each, with an order name, stage number, and order date. 2.The rental house delivers the equipment to the stage, connected to the order name, stage, and date, and email an ORDER OUT to me, with the discreet SKU of most items, but with only COUNTS of some items (ie, "75 aircraft safety cables" instead of "SN1234567 Arri 300W fresnel" ). This comes as both pdf and excel, though I have little control on the formatting and accept a fair bit of labor in the conversion. 3.There is a data entry step to import that ORDER OUT into my system 4. My crew scans (with a barcode scanner) the entire order to confirm it is delivered as promised, including entering the manual counts for non-SKU items. This step should be somewhat idiot proof. 4 1/2. We shoot some tv. 5. My crew later returns some or all of the order, again scanning it and recording manual counts for non-SKU items. This step should be somewhat idiot proof. 6. The rental house emails an ORDER IN to me, basically a parallel to the ORDER OUT earlier. I need to be able to at any time see the original order, what's been returned (as both individual SKU's and also summed by item type), what remains of an order (as both individual SKU's and also summed by item type), and similarly see inventory on the stages as wholes. I also need to easily compare all the above steps--did the rental house send me as many widgets as I ordered? Did they confirm as many widgets returned as I scanned as returned? How many widgets are missing? How many widgets are on the stage somewhere? Plus of course to search for an SKU and see what order it is from and what stage it ought to be on. While scanning in/out I need confirmation that the SKU I've scanned is a match to something the rental house sent us (and a warning that it's an error somehow if not) as well as a dropdown to select an item description for a non-SKU item. I also need a warning if an SKU is scanned twice--this process is a little mind numbing and sometimes the guy with the scanner beeps a cable twice, and we need to know that... A complication to this is that while the items with SKU's DO have unique SKU's, we sometimes order an item on one order, use it, return it, and later that season order the item again and coincidentally receive the same item with the same SKU, but now on a new order... so we need to differentiate a duplicate scan of an SKU that is on THIS order vs a duplicate scan on any order ever. Since multiple orders end up on the same stage at the same time, though, the various orders for the entire season have to cohabitate in one database. I built a real pain in the neck excel document that does a lot of this, because that's the program I'm used to from other applications, but after bleeding from the ears with nested arrays and tier upon tier of IF;THEN formula it occurred to me that I'm really not processing this data at all, just comparing long lists of records and bare-bones addition/subtraction and that it is likely more suited to a database than spreadsheet tool. Obviously there exist inventory management programs which natively do this, but they either cost an ongoing subscription or have very limited asset numbers which make them unsuitable for me (we're tracking between 1,000-10,000 items usually), and they also offer a lot of invoicing and stockkeeping that I don't need. Is this something Filemaker is comfortably suited to, or am I barking up the wrong tree? Thank you tremendously for taking the time to read this! Luke in NYC