Order# Counter for customer

[Alexsus]

Hi Guys, This might be really easy to solve but I have been awake for 36 hrs and cant seem to picture the solution.

I have a marketing database in which I input orders for customers with all normal related fields, such as order date, products ordered, what store, coupon used and so forth.

I am now creating automatic mailing lists and got stuck on one criteria, "number of orders" for a particular customer. How can I create a scheme where when I create a new order for a customore it gives it an order number relvant to the # of times previously ordered. For example if its a brand new customer, the counter will start at zero. If its a customer already in the database who has placed orders before it need to look up the last "order#" value and add +1 to it. I cant seem to create this function or script. I would like to have this filed be autoenter so that when a new record is created, depending on past order history, it autocalulates the current order # and inputs it. This way I could to a maling list for customers who rank high in the # of orders they place..

Please help

Alex

[LaRetta]

Hi Alex,

Is the Order Number your unique ID as well? If you have many staff inputting orders (multi-user), I wouldn't suggest pulling the order count into the order number. It isn't dependable because if two Users create a record at the same time (but have not yet committed the record), your count will be incorrect.

This way I could to a maling list for customers who rank high in the # of orders they place..

It might work better to leave the counts OUT of the order number and instead address that need when your report is created. If you would consider alternatives let us know. You can sort by aggregate or even write to Rank field. There are many options here ... :wink2:

[Alexsus]

Well I dont have an order# field created yet. I was was trying to create one and using the auto-enter features to desiging a custom function (prolly some sort of caculation) which did what I am trying to do.

Basically I need to know how many times customers are placing orders with me. I dont need to have a feild that does an autoenter and I can do this like you said, when I generate my reports or mailing lables. But can you suggest a script to me?

The order# is not something specific I had in mind. It was just goin to be a seqeuntial count that upped itself everytime by +1 when a new order for a previous customer was present.

Let me know what kind of ideas you have?

Please

[LaRetta]

I hope you are structured properly, ie, unique IDs binding your relationships. Your Order Number should indeed be an auto-enter serial (or auto-enter calc). I will not debate whether an auto-enter calc should be number or text because MY jury is still out on that one. There are Pros and Cons both directions and times that both are appropriate. On my 'big 3' tables (customers, invoices and products), I have serial (number) AND auto-enter calc (text).

As for grabbing the rank total, functions such as Count(), Sum() ... aggregate functions ... can sort quickly particularly if number or date. I have one layout with 150 records which displays 15 aggregate fields in columnar form. Sort takes approx. 3 seconds across network (7.0v3 tests). Depending upon the record volume you anticipate, sort would be my suggestion. If you are dealing with very large numbers of records, you might consider writing the counts as static data. I have found that setting fields takes a bit longer but it decreases the 'refresh' time when you create your report. Only you will know which method would work best.

My layout (with all the aggregate) is required because it must be viewable in Browse mode. You don't have that constraint. All the pause you will experience is at the time the report is generated. This is a small time-period overall. Also, writing to Rank fields doesn't provide history, ie, if an error is made because script (or computer) freezes in the middle, you have no way of knowing what the total should be. I've done this one. So writing aggregate to static field has it's shortcomings as well. If you wish to try sorting an aggregate (and keeping in mind I don't know your structure), create a calculation (number) with:

Count ( yourRelatedTable::always non-emptyField )

Usually this non-empty field is your serial or uniqueID but any field will work (as long as it is never empty). Then sort your table by it. You'll have your answers soon enough. Note also that there have been speed shifts (in sorting server/client side) in some of the various versions from 7.0v1 through 8.0v2.

There are other wonderful people on this forum who may have even better ideas. I just know imbedding the count in your Order Number is NOT the best way to go. So if you fill us in on some of these other aspects of your structure xor explain whether networked and specific FM version and expected record counts xor simply tell us which method you would prefer, we 'all will help you through it.

LaRetta :wink2:

[LaRetta]

I just thought ... I was focused on your report being in your Customers table. But it doesn't need to be because they are related anyway, right? So generate your report in your Orders table and only display the sub-sum part sorted by (and based upon) Orders::CustomerID. This leading part can contain your Customer fields as well. You don't need a body with the Orders listed. This should be very quick.

Since most of my reporting must be Browse-able, I didn't think of this for you. My apologies. I'm sure others would have caught my error. :)

[Alexsus]

Hi Laretta,

I am not understanding your solution fully. Let me get into a bit more details.

I have two tables. Table1 is Customer records and it contains Customer name, address, ph, email, blah blah. Table2 is Order records and it contains name Order name, Products ordered, Date orders, Total Order amount, coupons uses and so forth.

Table1 and Table2 are related via name. Meaning Customer Name = Order Name

Now I have a layout called mailing list generation, which is basically diffrent fields like zip code, products orders, coupons used and basically when you enter this layout you start off in find mode, enter the fields you want to search in. This generates mailing labels based on the found names and their respective addresses being retrived from the customer records table (Table1)

Now I want to add another field to this mailing list generation layout, where I can for example, put in 15 or 20 or <20 or >30 or a range of values that correlate to the number of times a customer has placed an order with us. Thats why I thought of using a new field called ordercounter with autoenter that uses a custom fucntion to represent the number that will be +1 everytime a new order is detected on a previous customer. I am just have a hard time establising that custom function.... Or is there a better way?? I hope you understood me here...

Alex

[mz123]

I think you can just set up a Summary field in our Order table, let's say "CountSummary" and it would be a Count of Order Name.

If your labels and order table are related by Order Name, then in the labels layout put the field OrderTable::CountSummary. That should do it....

I hope that solves your problem!

Martha

[mz123]

I'm attaching a sample of what I mean!

However... This won't allow searching in the field...

TEST.fp7.zip

Edited May 12, 2006 by Guest

[Alexsus]

I looked at your test file and although I do understand your logic, I am not certain it accomplished what I needed.

On my mailing layout where I search certain fields to generate labels, one of the searcable fields has to "# of orders". In this field I want to be able to put values like <20, >50, 10...30, that will then bring up customers who have ordered # of times as per my serach value.

For example. lets say there is a customer called Joe Black. I set him up for the first time in my customers database, therefore his order count would be zero. Then I place an order for him through my "orders" table which is related to customer table via name. This order table should now show the ordercount feild as 1. The next time I place an order for him this fieild would be now 2 and so forth (automatic serialization by +1). But the catch is everytime the customer changes, I need a function or a script that will first look to see if this customer has ordered before and if so what was his last order count, then increment that by one.

This field will then be searchable so that I can generate mailing labels for customer to order the most or whatever # I specify in the search criteria.

Does this make any sense?? Is my logic wrong here?

Alex

[sbg2]

Forget the serialization by last order number, just use an auto-enter serial number.

Example attached

To get the count from the Orders layout - in Relationships create another occurence of the Order table (for this example named OrderNum2). Relate the first Order table to the new occurence where Order::id_Customer = OrderNum2::id_Customer. Create a new unstored calc field in the Order table that returns a number = Count(OrderNum2::id_Customer).

To get the count from the Customer database - relate Customer::ID_Customer to Order::id_Customer. Create a new unstored calc field in the Order table that returns a number = Count(Order::id_Customer).

DB.zip

[John Mark Osborne]

It seems like sbg2 and LaRetta have nailed this one down. I read through the entire thread and it seems like the Count function will do the trick. Have you tried using the Count function, Alexsus? What I think you need to add to the really great example file sbg2 provided is a Case statement determining if the customer is greater than 20 orders, greater than 50, etc. It might look like this:

Let(

OrdersTotal = Count(MYTABLE::CustomerID);

Case(

OrdersTotal > 100; ">100";

OrdersTotal > 90; ">90";

OrdersTotal > 80; ">80";

OrdersTotal > 70; ">70";

OrdersTotal > 60; ">60";

OrdersTotal > 50; ">50";

OrdersTotal > 40; ">40";

OrdersTotal > 30; ">30";

OrdersTotal > 20; ">20";

"<20"

)

)

[LaRetta]

where I can for example, put in 15 or 20 or <20 or >30 or a range of values that correlate to the number of times a customer has placed an order with us.

Just perform a normal search on the calc you create in Customers [color:blue]Count (Orders::OrderName). A second calc isn't really needed. This calc is number and adheres to all normal FM search methods on numbers, including ranges. It'll be a bit slow is all (as I mentioned before).

BTW, you really shouldn't be using your CustomerName as your match key. You should be using a UniqueID - an auto-enter SERIAL at least. This is important because if a customer changes their name (and they will), all their orders would disappear. Or, if a user enters a new customer incorrectly and creates an order, then later corrects their mistake, the order will disappear.

So again, you don't need a second table occurrence of Orders. Just place the calc in your Customers file. Then place this calc field on your mailing form. Then search for the 'group' of customers you want, ie, >20 or 20..40 and send your mailing.

LaRetta

[Genx]

Boy did i have problems with making bad choices regarding unique keys at the begining. It is definitley a good idea to not let the user change the primary key after its been entered.