Complex Inventory - Need some lights...

[Ugo DI LUCA]

Hi all,

Let me try to explain it a bit.

Business Tile Wholesaling.

Specifics : As for many other Manufactured goods, and specialy when dealing with colors, each production (from 50sqm up to 50,000sqm) will be different from the previous.

With the introduction of some ISO standards, each product is stamped with several codes, and wholesalers following ISO should also integrate these codes when re-selling any item.

Apart for quality control, it offers a guaranty that we won't be selling items coming from different productions, thus having different caracteristics (size and colors may vary during the maufacturing process). This also gives the customer a way to identify the correct item when they need to place a complementary order, so that the most suitable product would be sold to them, according to what they were sold at first.

Even if different from process to process, this item remains unique by identification.

This mean we need to run separate inventories inside each inventory .

If a customer place a 20sqm order for product X, even if we have 70 sqm in stock for this item, we won't be able to sell anything from stock if we don't have at least 20 sqm with same code.

The Product File holds 70,000+ products and we currently have 700 items in stock. We have a newly created Tarnsactional Invetory File, and the last inventory count returned 3,025 different codes in fact.

When placing an order, I'd like a portal to display the given available products according to the quantity required. I'm currently using a loop script, but I find it painful and absolutely not dynamic.

Also, we may sell the closest codes availables.

The codes are in fact very helpful. Basically a choice-size-shade code as Product X -1-6-JPM8F.

If a customer asks for a refil for Product X -1-6-JPM8F and it's not available, we could with previous mention to the customer, propose :

Product X -1-5-JPM8F

or

Product X -1-6-MPR9L

Any idea ?

[spb]

It looks like you'll need to make a separate database to hold the various codes & inventory associated with each product. With some tricky work with calc fields & relationships, you should be able to display available inventory.

If you have a (global?) entry field in your primary inventory database for area (say, 20 sqm), then perhaps a script is triggered after this field is filled, setting a work field to Product & 20 (this way it won't be a calc field for use in left side of relationship).

Over in the new database, you have a calc field that concatenates Product with the global entry field via a constant relationship. This calc is defined in such a way that all like items with on-hand inventory equal to or greater than 20 sqm are set, otherwise field remains empty.

Then in your primary inventory db, you have a button that does a simple GoToRelatedRecords, plus appropriate sorting, layout choice, etc. There you are, with a list of all versions of the product with sufficient inventory on hand.

Making this into a portal in the main inventory db might save some steps, but I'm not so good with portals.

Steve Brown

[Ugo DI LUCA]

Hi Steve, thanks for your input.

Yes a concanation involving a global at right side through a constant is working. That is quite exactly what I've been using before the loop script.

Each stock purchase is registered as a separate line. As a same exact product (product + codes) can be purchased and stocked several times, a separate script step at the moment of purchase/sale confirmation

- set the 'QtyOnHold' using a calculation based on a SelfJoinOnExactProductCode.

- set an alternate key ProductIDAlternative to the ProductID value.

I used a Relationship Product::ProductIDAlternative to have only the latest entries for each Product+Code being displayed, as well as their respective 'QtyOnHold', and your c_checkQty (boolean unstored) to sort and highlight those suitable stocked items.

I'm now using a loop script for a left side multiline key, that concanates the ProductID, the Codes (for a refill request only) and the Quantity.

The loop set each Quantity up to one, starting from the given requested quantity (20 here) ending when it has reached the Max(::ProductID:QtyOnHold), and gets pretty much tedious (nested loops) to set when I need to consider the codes too, so that this key, for the simplest, looks like :

Product X 20

Product X 21

Product X 22

As the real quantity would rather be 151,18 sqm, I used a Int(QtyOnHold) to have it rounded to limit range needs, and a concanation ProductID&" "&QtyOnHold.

Well, it works. The portal is sorted ascending so that I'd pick the older stock at first, but isn't that dynamic as I expected it to be, even if quicker than the first method.

I tried myself with SmartRanges, but if I succeeded in having ranges for dates or time, I'm clueless on how to concanate a number and the Product Code.

Again Steve, thanks. It's good to know you're still here, reading and understanding my complicated cases, and even more to know we've got crossed ideas...

Anyone willing to help on this range set ?

[spb]

Ugo DI LUCA said:

It's good to know you're still here, reading and understanding my complicated cases,

Ugo: You're the guy I'd to go to get my own complicated cases explained. I feel like a medical student explaining procedure to a surgeon!

But in your range problem, I can see where doing all that looping could be a huge pain, and possibly account for performance drag on your portal. Rather than loop & increment every product with a one-unit quantity successively greater than the desired quantity to form your multi-line key, why not come up with a text symbol of some sort to represent products with quantities greater than or equal to desired? Let's use XX.

You could enter the desired quantity in a global (g_Desired), say 20. Now you have a calc field somewhere (c_Available) that calcs:

Case (QtyOnHand >= g_Desired, "XX", "")

This calc field will be either empty or contain XX if there is sufficient quantity of a product available. Now you concatenate with ProductCode & c_Available to tag available products. To make the multiline key field, run a script that creates a found set of all records with XX in c_Available, go to a list layout displaying only the concat field, select all & copy, go to a layout containing the multiline key global field & paste.

This keeps the left side of the relationship free of calc fields, but it's not too dynamic. As soon as things change, the copy & paste script needs to be rerun. This way may cause you even *more* performance drag! But perhaps there is something in there that may give you an idea.

Steve Brown