Stock In/Out by Expiration Date

[LaRetta]

Hi everyone,

Well, I need to change our inventory structure. Originally, there were only two 'expirations' of stock (Current and Old) so I created fields for them (in the Product table) instead of another table. As inventory is pulled (entered in LineItems), it summarizes in the Inventory table (using SecureFM event trigger). From there, system checks the Old field (in Products) and pulls from there until exhausted. New stock added (in Inventory) first moves the New to the Old and then writes to the New (we never had overlaps).

My reasoning for the constant writes? Size of the files and slow searches and summaries. And we post every day (our orders ARE our invoices) and since I have the LineItems isolated during the shipping process, it works well to write them at that time (it's almost instantaneous to handle then, and staff get's their instant displays). Anyway, we didn't account for customer returns (Lord forbid) and now two stock fields (for new and old stock) might go as high as 5!

Under the theory of first in - first out, a relational structure seems fine because it will automatically relate to the first record. BUT ... if stock is returned, older stock may end up lower in the table. Lookups can use next higher value (ExpireDate MM/YY ??) but then again ... this is scripted and I hate the thought of sorting my inventory every day (it's huge) just to get them in sequence.

Because our Invoice LineItems are 600,000 records, I've been writing the product count to the Inventory table which daily summarizes them by product (and now will also need to include the ExpireDate in the key). LineItems is related to Inventory on ProductID & " " & ShipDate (using Allow Creation of Related) and it simply sets the quantity field in Inventory (or creates a new ProductID/Date as needed. I then use the Inventory table to generate portals (cross-tab style) by day, week, month, quarter and year. I admit, with all the design challenges in FileMaker, working with my Inventory and LineItems is the most frustrating.

Can someone give me a jump-start in a good approach on how to script LineItem selection based upon oldest stock (to insert the ExpireDate and Lot# into the LineItem for tracking on an order), AND ... decreasing my stock on hand from the oldest stock pool?

I'll take the ideas and do the work but I'm unsure how to best approach the problem. Ideas greatly appreciated!

LaRetta

[BobWeaver]

I didn't completely follow what you were saying, but you should be able to add a relationship sorted by date ascending, and then the record that is accessed by the relationship will be the oldest one.

[LaRetta]

Hi Bob,

So wonderful to see you around! I've missed your great feedback!

I admit my post was probably unclear; I was a bit tired.

Many of our products have no expiration date (used to but newer stock doesn't), so sorting the relationship wasn't working (because blank dates appeared at the top when sorting ascending).

Creating a text calc which displays MMYY then sorting the relationship in descending order on the text field, the blanks appear at the bottom but the sort is not accurate either.

I guess I've never paid that much attention to how the different field types sort empty fields, ie, dates and numbers put them at the top and text puts them at the bottom (when ascending sort).

I guess I'll have to assign a date to every stock item (whether it expires or not); unless you have other ideas for me on how to force the sort sequence.

LaRetta

[comment]

Choose ( isEmpty ( ExpiryDate ) ; ExpiryDate ; Date ( 12 ; 31 ; 4000 ) )

[LaRetta]

Since the Expiry Dates are listed as 0407 (validated to remain 4-character, only numbers), I had created a Date field (Date Expired) converting that to a date (the first of the month). So I modified that Auto-Enter (Replace) calculation to include yours. It works perfectly. If it can be tweaked further, I'd appreciate it!!

Choose ( IsEmpty (ExpMoYr) ; Date( Left(ExpMoYr; 2); 1; "20" & Right(ExpMoYr; 2)) ; Date ( 12 ; 31 ; 4000 ) )

Thanks for the help!

LaRetta

[comment]

If I understand correctly that 0407 is July 2004, then:

Choose ( isEmpty ( ExpMoYr ) ; ExpMoYr ; 9999 )

This is of course limited to the present century.

--- EDIT ---

Just realized that ExpMoYr must be text, so:

Choose ( isEmpty ( ExpMoYr ) ; ExpMoYr ; "9999" )

In general, I am a bit wary of text fields with numerical content sorting as expected. My preference would be using a number or a real date. IIRC, a date can be formatted to display in the same manner (can't test it right now).

[LaRetta]

"I am a bit wary of text fields with numerical content sorting as expected."

Agreed! And because our expirations must actually EXPIRE a product and not allow shipment, I must use real dates which can match to physical ship dates. And that's why I've created the date field. The ExpDates that appear on our products are 0407 (April 1, 2007). I'e kept this format because we import our stock in directly from different manufacturers who all use different formats and so I just convert them (Auto-Enter Replace).

I suppose I could just convert to standard date but then I'd have to create a calc to convert back to MM-YY format anyway to displasy on packing lists, invoices, labels etc. Either way, seems I'll need two fields (MMYY text field and date field).

We are fighting bottling and labeling regulations on the Expiry Dates and what laws require an expiration ... I have no consistency to work with at the moment. Next week, we may again be required to have dates stamped on everything (and thus have ExpDates with every Lot#).

The Auto-Enter date calculation (Replace) allows proper tracking and pulling by date; but also allows plugging in a real ExpDate (MMYY) if needed - without further change to the system. And now it sorts (and relates) correctly.

So, unless I can improve on my calc above (converting the MMYY text to a date) to use within your Choose(), I think I'm set to go. Thank you!

LaRetta

[comment]

I suppose I could just convert to standard date but then I'd have to create a calc to convert back to MM-YY format

No, you wouldn't. You could use the same date field again, formatted as MM-YY, or YYMM, or whatever you like - see attached pic.

[LaRetta]

Silly that I forgot about field format for changing the date display - I've used it before too!

I ADORE eliminating excess in my solutions. Thank you!

LaRetta