Question - should it go in "Code Repository" section?

[HotFrost]

This is not a question about code or anything...
I would like to open a discussion of the best code or algorithm for
making an order repetitive one.

I mean:
- there is an order with order description;
- company has to fullfill that order on repetitive basis, say every thursday for like 2 months;
- instead of puting this order manually every thursday, and remember it, it would be nice to create some procedure that will make this order repetitive.

How would you go about this problem?
What kind of algorithm would you suggest?
Make a special table of repetitive orders and then work with it? Or
Actually create a new record of this order description in Orders table for every repetitive day?
How will you remind a user that this is the last day of repetitive sequence?
And so on...


As from my side, i have developed the algorithm and it works, one can make an order repetitive, any number of days of the week, for any future months, with reminder of the last day in the sequence..
If anybody is interested in this discussion, i will develop more on this subject, and tell about my way of doing this.

Why did i create this thread?
- i believe my code can be improved or totally redesigned if somebody has some bright ideas i have been unaware of. I would like to polish it, and make it accesable to everyone to use. I mean it. If anybody needs it...Thanks.

 

[HotFrost]

Just a ScreenShot from my Repetitve Orders Form

In case you think i am..you know...

 

[ReAn]

Two tables,

tblOrderTemplates (where you store the order information)
tblRepititions (where you store the repitition info and the templateid)

Make some vba code that checks what orders need to be put in each day and then make the code add those orders.

Make a macro that runs the code.

Drag the macro out onto your desktop, now you have a shortcut.

Goto Accessories->System Tools->Scheduled Tasks

Now create a scheduled task to run that shortcut every day.

 

[HotFrost]

More explanations please...

hmm..
so there is a macro that you run with windows scheduler.OK. Got that.

Personally i think it is a bad idea, to make a user run a some macro from his/her desktop. isn't he already busy enough? then look after that shortcut..

The idea about two tables....
I don't uderstand why do you need the first table - orderstemplate -
just give to the second table tblRepetitionInfo the record number from the Orders table - to keep the track of the order needs to be made repetitive.

Then - is it better to put repetitive order (using some code) in the order's table each day the order is schedule...or make all the records in advance ?

More suggestions..please..

 

[ReAn]

Personally i think it is a bad idea, to make a user run a some macro from his/her desktop. isn't he already busy enough? then look after that shortcut..

This Macro shortcut is what you make the scheduled task run, you can put it anywhere but it's neccessary to make the scheduled task that runs it automatically.

The idea about two tables....
I don't uderstand why do you need the first table - orderstemplate -
just give to the second table tblRepetitionInfo the record number from the Orders table - to keep the track of the order needs to be made repetitive.

The template would hold the base information, and things like dates/times would be filled in dynamically and added into the main table, this keeps accurate records of each transaction as opposed to relying on the original order to remain 100% the same. It's to keep things consistant.

Then - is it better to put repetitive order (using some code) in the order's table each day the order is schedule...or make all the records in advance ?

More suggestions..please..

Every day, it should only generate the orders from the template that need to be put in that day. Otherwise it would generate the advanced orders more than once..

 

[dan-cat]

I mean:
- there is an order with order description;
- company has to fullfill that order on repetitive basis, say every thursday for like 2 months;
- instead of puting this order manually every thursday, and remember it, it would be nice to create some procedure that will make this order repetitive.

How would you go about this problem?
What kind of algorithm would you suggest?
Make a special table of repetitive orders and then work with it? Or
Actually create a new record of this order description in Orders table for every repetitive day?
How will you remind a user that this is the last day of repetitive sequence?
And so on...
.

I would add one extra table which holds a reference to a past order plus three extra fields. Start Date, Expiry Date and DayFrequency. This would act as a template. If the order is dead and buried then it should not be changed - ie. once it is invoiced - you should lock it - I've known quite a lot of accountant skullduggery occur in this way. (changing of orderdates to alter due payment dates)

Thus if one template order has a frequency of 3 days, you could easily calculate the date of each third day, between your start and expiry date. If your system date equals one of your calculated days then trigger the creation of the order (a simple append action would do it). Any expired template orders could either be deleted, or the user prompted for action.

You would have to decide how the appropriate event is fired. Is it done with an autoexec, or do you rely on the user to trigger the event daily? Some users may not like the idea of orders being placed without human confirmation first but what if someone goes off sick for a week?

You don't have to store all the order details within the order template table - you could just reference your template to a current order - however having a seperate order template table would be easier to administer - ie. your code would not have to go through your entire order table to check for current templated orders - it would just have to check your order template table which undoubtedly would be much smaller and thus quicker.

 

[HotFrost]

Great ideas..guys..

One approach is clear here:
1. Take an order and copy it into OrdersTemplate table.
2. Three more fields in that table
3. Check every day if anyorder needs to be put into the OrdersTable...

Ok, i promised to tell you how i did go about it..here you are...

I have created a special field called 'repetitivesequence' for each order in order table. It is a text field.
The default value was '0000000000' - there is 10 places - 7 for each day of the week, 2 for sequence order - every week, everyother week and every month and the last of it- indicator if the order is the last one in sequence.

If a user decided to make this order repetitive - he chooses the sequence, (look at the screenshot i attached earlier), i just place this order in the prearranged sequence in the OrdeRtable in calculated dates - all the holidays are being taken care of. The last order i place has the code - like 1101100111 discribing the sequince it belongs.

No warries for accountant - all orders are placed. if fullfilled - billed, if not - deleted.
Lastly, when the applications starts, i am checking if the orders, scheduled for today, have the 'SequenceOver' flag ON- in this case - i am giving the user choice - ignore it - or look what kind of sequence it was - and continue the sequence.....In conclusion - no extra tables needed, no shortcuts, no macros, one additional small field in order table. What do you think? ANY CRITIQUE WILL BE VERY MUCH APPRECIATED..

Hot Frost

 

[Mike375]

I am sure I missing a lot here so...

Why can't you have a Date() field and Weekday and Thursday is day 5. For the 2 months add 60 days todays date and so things are triggered that way unto the date in 60 days arrives. Combine an IIF with Weekday to select the records/clients based on some entry you make to establish repeat orders are required.

A macro OnCurrent or OnTimer could call up the records that meet the criteria etc.

Mike

 

[dan-cat]

It seems quite clever but I have a few questions...

1) How easy is to track how an order was generated? ie auto or manual. Is it easy to refer back to the original order without storing an order template within an extra table. What if a user makes a blunder and installs a repetitive order in error - can you track the error down?

2) It seems that you have to check every record in your order table to see whether the generation of a new record is appropriate - it could perhaps be quicker if you have a template order table detailing specific orders due for generation. If you only have three valid order templates to check - surely your code would run quicker.

Just some thoughts - but I like your style though.

 

[Rich]

I am sure I missing a lot here so...

Why can't you have a Date() field and Weekday and Thursday is day 5. For the 2 months add 60 days todays date and so things are triggered that way unto the date in 60 days arrives. Combine an IIF with Weekday to select the records/clients based on some entry you make to establish repeat orders are required.

A macro OnCurrent or OnTimer could call up the records that meet the criteria etc.

Mike

you don't need a Weekday field or function to know what day it is, and unless I went to the wrong school 60days time doesn't mean two months

 

[Mike375]

you don't need a Weekday field or function to know what day it is, and unless I went to the wrong school 60days time doesn't mean two months

Is there an easier way than

WD: Weekday(Date())

What would you use for 2 months. Bills, invoices tend to be 30, 60, 90 days and so on.

Mike

 

[Rich]

Format([MyDate],"dddd")
and monthly can also mean Cal mnthly

 

[Mike375]

Format([MyDate],"dddd")
and monthly can also mean Cal mnthly

And how is that better. It did not work for me as it just gave the date that was in the field I inserted for [MyDate]

This WD: Weekday(Date()) gave me weekday number for the current date.

 

[Rich]

you forgot the =, and it will return the actual day, not the Day number, which you don't need in any case, it's not really relevant to this topic

 

[Mike375]

you forgot the =, and it will return the actual day, not the Day number, which you don't need in any case, it's not really relevant to this topic

But if I want something to happen every Thursday why is it not relevant.

If you make an entry for the client/order type or whatever that it is a repeat then the combination of that entry and weekday number is 5 will provided the trigger, correct???

 

[Rich]

The DateAdd function will do, provided the first date is on a Thursday

 

[HotFrost]

Answer To Dan-Cat

first of all, thanks Mike for the praises, here is what i think about your questions:

"1) How easy is to track how an order was generated? ie auto or manual. Is it easy to refer back to the original order without storing an order template within an extra table. What if a user makes a blunder and installs a repetitive order in error - can you track the error down?"

I don't see why should i make the difference in how the order was generated - the all fuss is just about save the user's time;doesn't matter how the order was generated.
If he makes a blunder..no, there is no undo function..- but it is a good idea...to have a form displaying all the orders just created with the last question - commit?-run append query or undo.. so..he can double check.

2) It seems that you have to check every record in your order table to see whether the generation of a new record is appropriate - it could perhaps be quicker if you have a template order table detailing specific orders due for generation. If you only have three valid order templates to check - surely your code would run quicker.

No, you don't have to. you have one order chosen - then make it repetitive.
once it worked out, on the load application event - you just check the orders for this date..nor every one.

ok..any other thoughts? on this subject...

 

[dan-cat]

This is me speaking from experience here which in no way reflects on what you are trying to achieve or saying what you are trying to do is wrong but I would like to say the following...

a) I can almost guarantee that a customer will want to know how and why every order is generated. As soon as an order is generated in error due to user error - their boss WILL want to know how the error occurred and to prevent it from happening again. You can bet your bottom dollar they will throw away the 'saving the user time' line of thought in place of being able to track HOW each order is generated. You have to remember this is THEIR business we are talking about here - and they will not be happy if or when 'phantom' sales are generated - you the programmer can't give them a complete track history of how it occured.

Basically if you build some kind of tracking system on how each order was generated and by whom then I'd be happy.

P.S My name is Dan not Mike