Item Performance

[Anuviel]

I have an item, line items and sales tables. Everything works nicely and exactly as it should.

They are related as follows:

Item::ItemID = LineItems::ItemID

LineItems::PO = Sales::PO

In Item table I have a field that sums the Quantity from LineItems and gives me the total quantity for the item over all Sales regardless of PO. So if PO 1 sells 10 of item x and PO 2 sells 90 of item X when I look at item X in Item table I will see 100 of item X sold.

The problem:

Item X will eventually be discontinued. After it is discontinued I need to reuse its code for a new item. When I create a new item with the same code it shows 100 sold - how do I avoid, prevent or fix that from happening? I know why it shows 100 but I do not know how to make it so it does not, short of deleting all LineItem records for Item X.

I need to be able to reuse Item codes to things out of my control.

[comment]

Use another field for the item code. Use an auto-entered serial ItemID field for relationships.

[Anuviel]

Thanks. Not sure how to apply that. If I use an autoenter ItemID how does that work?

IE.

Items::ItemID = LineItems::ItemID

When I type ItemCode into the portal on Sales layout nothing comes up. If I type ItemID the information comes up as it should.

Sorry, I feel I should get this but I do not, will think on it more but if you could post a small sample file or just write it a bit more...

Thank you very much.

[comment]

I don't understand your question. If the item code is reusable (i.e. not unique), you cannot use it for your relationships. That's what started this. You can still use it to find records, or even in an auxiliary relationship (for the same purpose). But if you want users to type the code into a portal in order to create a new lineitem - well, how is the system going to know you mean the "new" product, not the discontinued one?

[Devyn]

What about a lookup for the line item fields that autofill from the Items table. Use a second occurence of your Items table for the lookup relationship (via the ItemID that must be re-used when a product is discontinued).

The lookup will occur when the user enters the ItemID, but it will not auto-update if the Items table is changed (that might be a down-side) without a re-lookup on the ItemID.

Like the last poster said, leave the line items connected to the Items table permanently via an auto-enter serial number that has nothing to do with your in-house ItemIDs. Use it to collect your sales data.

[Anuviel]

well, how is the system going to know you mean the "new" product, not the discontinued one?

That would not be the problem. Once the item is discontinued it gets deleted (voided and copied to Void Items table for purposes of keeping history).

This is the work-flow, might give you a better idea.

Item A001 is in Product table. In sales table, once the item is sold an invoice is made and A001 is entered into the portal thus creating LineItem. Once the code is entered the rest of the information gets looked-up and automatically is filled in. In products table a quantity of item A001 is automatically entered (summary for all LineItems that gives total of A001 sold in all LineItems). That part you already know - just wanted to clarify.

Once the A001 is dicontinued it gets deleted. New A001 is created after but as soon as it is created the total quantity is automatically entered due to previous A001 in LineItems table.

All this you already know - I tried making the calc unstored but it cannot be due to it being based on relationships and related records.

Here I attached the file. Ultimate goal, Once the Item A001 is deleted and a new A001 is created the Performance summary should be empty. See Performance fields in the middle of Product layout. Sales_Line_Items should be left intact once the product is deleted and a new one created with the same code. User name for the file is Anuviel and password is test.

Thanks so much, hope this will make it easier to understand - quite tough for me to explain exactly.

Test.zip

[Anuviel]

I tried that, it did not work.

K, sorry.

The lookup will occur when the user enters the ItemID, but it will not auto-update if the Items table is changed (that might be a down-side) without a re-lookup on the ItemID.

I tried that and and once the item table was changed (item deleted and re-created) the performance calc fired off again. The calc is based in Products table, maybe I should base it in LineItems?

Reason for edit:

03-24-08 01:25 PM - Post#285784

In response to Anuviel

Please do not respond with "It did not work", but instead, tell us what it didn't do, or did do, that didn't work for your needs.

Lee

Edited March 24, 2008 by Guest

[Lee Smith]

Please do not respond with "It did not work", but instead, tell us what it didn't do, or did do, that didn't work for your needs.

Lee

[comment]

If an item is marked as voided, you could use the mark to exclude it from relationship/s.

However, I still think that using a reusable code as a matchfield is not a good idea.

[Anuviel]

Thanks, I agree but the thing is that I do not know how else to achieve what I need. When a record is Voided it is not marked as Voided, it is deleted. Basically I have a Void records table and a script which copies a record into Void table then deletes the coped record from its table thus making it possible for me to create a new item with a same item number.

I advised that once the item is discontinued it is better to mark it as Voided and then move to next available code, for example, if A001 is discontinued then A002 would be the next item code but my boss wants to reuse A001 because he does not want to have numbers skipping on the shelves like A001 A005 A007 if the numbers in-between have been discontinued. If there is away around that or any way to achieve what I need I am all ears. Thank you.

[Anuviel]

Now to think of it. Could this work. If I add an f_Key to the relationship on both sides and have the item code and f_key for match fields?

f_key in Products table being auto-enter serial and f_key in LineItems being a lookup or autoenter or calce equaling f_key in Products?

I tried before but was not able to make it work - is it possible?

That way once a Product is created it would have to match both in order for lookups and Performance calc to work. Meaning that when I deleted item A001 and recreated it then it would be a unique record due to second field.

So is it possible and how do I make a relationship based on a calculation where key in LineItems is a lookup of a key from Products - how to make it fire and work?

Hope this makes sense.

[mr_vodka]

Thanks, I agree but the thing is that I do not know how else to achieve what I need. When a record is Voided it is not marked as Voided, it is deleted.

This is why I urged caution here:

http://fmforums.com/forum/showtopic.php?tid/193818

I think that the structure that you are proposing will lead to lots of headache later. How will you account for historical data?

[Anuviel]

Thanks, I know, you are right - however I need to try to do my best to achieve what my boss wants and he wants to reuse item codes. If after giving it my best I still cannot make it work I will tell him that and advise what is said here but until then I have to try. Historical data is not to much important as far as it goes to which items were discontinued and which were not. The only historical data he is interested in / needs is the invoice data for sales and purchases.

[comment]

Let your boss have his codes, just don't use them to relate records. What you describe is merely a symptom of the real problem - your ID's are not unique. Your deleted products leave orphans in the line items table, and when you reuse a code, the new product "adopts" its predecessor's children.

You can dance circles around it with filtering, lookups, you name it - but the real solution is unique IDs.

BTW, moving discontinued products to another table will become unnecessary too, once you have a unique ID.

There are ways to select a product by its code, and have the product's ID entered where it needs to go. That's a user-interface issue. But your core relationships must be based on unique ID's.

[Anuviel]

Agreed. Ok, I will base a relationship on a Unique ID, no problem. How do I solve the matter of filling the information in the LineItem table by entering an ItemId - you said it is a interface problem? Any tips on that - that is what I am after. Once the person that uses the db enters item Id into portal the rest has to come automatically.

thanks.

[comment]

There are about a million ways to do it. I always hesitate on this type of questions, because you need to be intimate with the workflow to give effective advice. For example, you could enter the code and lookup the ID (you need an auxiliary relationship for this, filtered to show only active items). Or select the items from a portal (filtered in the same way).

I am not even sure what the real meaning of these codes is. Perhaps you should have another table for them, and have the actual items be children of this table - with only one child allowed to be the 'active' child at any time.

[Anuviel]

Thanks. The lookup ID sounds good. How would that relationship be set up?

I changed my relationship to autoenter serial in Products table so now I have

Products::f_LineItems = Lineitems::f_Products

so basically I need one more relationship which will allow for the key to be looked-up once the ItemID is entered into the portal. That is the way I would go as the other ways seem a bit complicated to me. Not quite there yet but going the right way - I hope.

[Anuviel]

Thanks. Figured out a relationship for the lookup. Made one more where ItemId = ItemId and once I enter ItemID into a portal the f_key gets looked-up and thus completes the relationship. Thanks so much.

[Anuviel]

Awesome. Due to this I also remade relationship between my Item and Product tables.

I have a much better understanding of relationships and unique keys now.

Thanks to all that helped and had patience with me.

Thank you.,

Anuviel