Show customers orders (1 Viewer)

[miked18]

Hello again, Looking for some help on another part of my db. in another post I mentioned adding orders into my db for new customers. Some of these will be reoccurring subscription orders. I need to be able to pull up a customer and see their future orders. (i would also like to be able to see a history of their orders too, maybe a separate lookup). there may be several different products order per date. Maybe this is shown in a subform? So my first question is, how do i show the above information? my next question is, I need to be able to edit future orders. To cancel, or change products or amount of products, or delivery frequency. If the customer is on a subscription, i need it to change all future orders as well. Another question, maybe the harder part... if a customer is on a biweekly subscription and wants to change to every 3 weeks. I would like to be able to make that change to a future order. once changed, all biweekly orders are either deleted or updated to every 3 weeks instead.

 

[theDBguy]

I would suggest not generating future orders until they are actually fulfilled. You can always generate a view of all future orders, if you need it for a report. But by not creating future orders, you can avoid having to update those records when something gets changed.

 

[Pat Hartman]

To continue the concept. You would run a query every month that generates the next fulfillment record for each customer. The renewal period would be specified on the customer's subscription record.

If you have multiple subscription periods, you might have to run the process weekly to identify which subscriptions need to be fulfilled next week rather than doing it once each month. The query to do this selects the most recent fulfillment record for each subscription and adds the period to that date. If the result falls in next week, create the fulfillment record.

 

[miked18]

I would suggest not generating future orders until they are actually fulfilled. You can always generate a view of all future orders, if you need it for a report. But by not creating future orders, you can avoid having to update those records when something gets changed.

I grow microgreens, so I have to put in these orders ahead of time so I can plan for my planting periods and quantities. but i get you point about not having to update the records if they get changed. thanks

 

[Pat Hartman]

You can still create projections without making physical records.

 

[The_Doc_Man]

There ALWAYS has to be a balance when making projections. I once worked for a company that almost collapsed financially because of someone's overly zealous far-reaching projections that didn't materialize - but the money to ramp up the company had already been spent and couldn't be recouped. (So the real culprit was the CEO who believed the marketing idiot who made the projections.) If the company had not been bought out ("white knight" takeover), I would have been out of a job a year or two earlier than when I actually left.

 

[Pat Hartman]

Except that these projections are based on existing orders. You can't sell cucumbers or pot unless you plant it and give it time to grow