Related calculation with no related records !!

[Ugo DI LUCA]

Hi,

Case for this sunny week-end ....

[color:"red"] Objectives : Determines the minimum quantity to keep on hold.

[color:"red"] Implementation : Give an alert and place a replenishment order instantanously....

Same calc, with some adjustment to be finally also used for Sales Management.

[color:"red"] Caracteristics : based upon certain "sales history", must give a "true" result , ignoring any "odd" sales increase to retrieve the exact "sales normality" for a given item....

[color:"red"] Some basic parameters :

1.Current year, Past year and Past two years sales for that item (by month, quarter, and year - totals and average) mixed at "the infinite" to recover by comparisons the "real normality"

2. Global Management Commercial Year increases objectives (by item, category of items or total records)

3. Given Management Sudden Decision

[color:"red"] Previous experience feedback :

I already did this kind of job once with my proper db and remember how painful it was at that time. I'm currently using the Line Items for the related part of the calculation. According to the number of records for a given item, it may take a long time (up to 30 seconds in some case) before FM refresh...

[color:"red"] Other Problems :

No Prior FM db implemented with sales in this Company for that project, thus no related records (at least before the next quarter)

[color:"red"] Left Brain ? :

Of course, I'd not recommand any of you to have to deal with this and I wouldn't say this is mainly a calc problem.

It may also be related to the relationship forum, but I thought "Left Brain" was the place for that post.

[color:"red"] Advice needed :

My first ideas about FM not being priorly implemented for sales tracking in this company would be to keep a statistic flat file at a start and to use it for a while, including this step into the calculation, which of course is already set (in my brain) to be a Case Statement....

With this idea came some others :

1. Would it be reliable to use a statistic files instead of the line items...?

2. Would a date range relationship somehow speed the calcs ?

3. Are there something obvious I'm missing here..?

[cjaeger]

you could create a statistics field in the products database which keeps the sales for this particular product.

Should be faster than a related statisctics database.

It is then updated by a script every time an item is sold.

Probably a good idea to seperate the statistics in a couple of repeats, so you can have the date, price, #of items separated & ready for a speedy calc with a repeat index field. And the sum() and average() funcs work with repeats faster than with relations.

[Ugo DI LUCA]

Hi Christian,

I get the concept. Times just came for me to check back my first db with FM 2.1 and repeating fields.

I'd need a script to update the stats (could be a end-month loop script) fields, but as the result would be ulteriorly coming from the Line Item, I still can play with related files for the current month sales "evolution"....or for a "DayToDay" checking, if I want some displays within portals or reports with sub-summary.

My only concern regarding these settings is the need for a clever control over what the script really updates, as I will be using (at least for the next 2 years) with :

- fixed stats fields (given by the client from his own statistics for the past 2 years)

- related records (at the moment the db starts).

Any suggestion about splitting this script in a 2 parts script, which one could be deleted easily in the furture ? I'm not that clear minded today !

But that remains a very good idea, Christian. And again a valuable example that repeating fields are still useful.

I'll back-up my files and run some tests to see how it works. Thanks.

Anny other comments ?

[Paolo]

I would do in this way:

for each item add two number field:

min_quantity

max_quantity

and a calculation field

quantity_to_be_ordered = if( quantity<min_quantity; max_quantity-quantity; 0 )

every week a script build up the order finding items with quantity_to_be_ordered>0

every month at night a script recalculates min_quantity and max_quantity for each item upon the item history.

thare are several algorithms and formulas to do that. They are in book about material requirement planning.

Basically min_quantity must be enought for the lead time between the date you send the order and the date you receive the material.

Max_quantity-min_quantity must be enought for a week (the period between orders).

Hope this helps!

[Ugo DI LUCA]

Grazie Paolo,

Ma.....I'm looking for a way to come up with a calculation for that Min calculation

This is what I'm stuck with, the alert message is "done".

Basically min_quantity must be enought for the lead time between the date you send the order and the date you receive the material.

Max_quantity-min_quantity must be enought for a week (the period between orders).

This also depends upon other criterias, as you can see that we are also working on the history, and aren't ordering 10 units at a time but (as a wholesaler) more than 10,000 units.

Thanks for any input though, even if I started with Christian solution with a very small test.

[Paolo]

Prego!!

This also depends upon other criterias, as you can see that we are also working on the history, and aren't ordering 10 units at a time but (as a wholesaler) more than 10,000 units.

off course the requirement planning (the way you calculate min and max) can be as complex as you want.

we have an MRP on AS400 for our wholesale company. The user have the ability to set up several parameters (lead time, time between orders, customer service level,.....) and apply them upon suppliers group, single supplier, product category, single product, and so on.

It looks at products history, isolate exceptional orders, recognise stagional products, looks at active orders (to the supplier) and active orders from the customers.

It does a really lot of things.

( Perhaps the interface is really ugly )

But everything leads to the calculation of min and max (and supplier lead time)...

Ciao!!

[Ugo DI LUCA]

Hi Paolo,

OK, this can be done, and as I said I've already done it for my proper files using relationships and calculation only....

But, the longest the list of product is going, the longest FM ask to give an answer before processing ANY order.

Again, my calcs are based upon some history/statistic datas and "live" ones.

As I am to redo the job for another project (my first as a developper), I wondered if there was another issue. And both of you are right. Your method implementing a daily script is OK except that if I have 3/4 orders for the same product (depending on the quantity), I may have to wait for the morning to know if I can process the order.

Following Christian suggestion about using repeating field instead, the script you suggest is leading me to the same method, that is extracting from my files some statistic datas, and then use some other relationship to get an answer if the Min is OK or not.

Now, as I'm starting this new FM db from scratch, the problem is I have no statistics coming from FM, so I will be working both upon some entered statistics and related files.

It's good to know that some understands the complexity of such work, and it's also good to know it works fine.

BTW, is your solution FM Built.

Ciao e buon calcio. Speriamo solo che non tifi per ....

[Paolo]

"Ciao e buon calcio. Speriamo solo che non tifi per ...."

no, non seguo il calcio (amo definirmi "calcisticamente ateo").

In bocca al lupo per il tuo progetto!