Filter what customers attend what shows?

yuppicide

I am trying to figure out, maybe there's an easy way to accomplish this. I am using Navision 3.10.

On our Sales Order form I have a textbox that shows "Event Code". I looked it up and there's a Form 50002 named Event Code. If I run that it just shows me the following:

CODE DESCRIPTION

The code is used to quickly shows what fashion show an order is from. Such as Surf Expo January 2012, would just say SURF0112. The description would say everything I wrote in the previous sentence. So, on the Sales Order it just shows SURF0112, but you can click the arrow it will show everything or allow you to select another show.

We'd like to figure out a way to use that information so we can call customers who frequent those shows. Maybe one customer visited us at Surf Expo in Jan 2011, but did not make an appointment for Jan 2012. We could easily find these customers and call them up.

So, currently I can go into posted invoices, I can then do a Filter on Event. This will show me customers who've placed an order.

Problem with that method is some customers do not place orders, or cancel an order. Yet we have a customer card open for them. We need to follow up with them.

My other option is to go into Customer Card, into Comments, I can then filter by *SURF* for example, then goto Comments, List, and it will show me Customer Number.

Problem with that method is I get a lot of duplicates.

Also, some customers may frequent multiple shows.

What do you think my best way to do this is if I can do it at all since I don't have access to C/AL Globals?

Maybe my only way is to make a new tab on my customer card labeled Shows? In there just make a bunch of text boxes that people can type in?

Alex_Chow

Your requirement is exactly what Relationship Management granule is designed for. You can setup the industry group as trade shows and create a segment on who attended.

Savatage

if you don't have that granule - how about a report by customer listing their shows over a user defind period?

or an event table or customer card that counts all Posted Invoices with each Event code (flowfield). Then you can click on the count to see each individual.

I assume the event code keeps increasing?
Are you doing say...
show a 2010
show a 2011
show a 2012

or do you just say "show a". Do you have the event code field transfering over to the posted invoice or better yet customer ledger entries?

Savatage

We do shows here too and we've created a "Vendor Extra Info" table.
It's not just called show because we use it for multiple things Catalog/Show/Rebate.
It's got a few fields
1) TYPE (Option) Catalog,Show,Rebate
2)Vendor No. Code10 **don't forget to set TableRelation::Vendor for lookup power
3)Year Code4
4)the rest are fields we need identifing but the top 3 should ge you going. You add you custom fields their

we then have a "Vendor Show Form" to show all the fields we have created using this table.
Then on the customer card we added a command button "Show Participation Info"
Push Action::RunObject
RunObject::Form Vendor Show Info
RunFormLink::Vendor No=FIELD(FILTER(No.)) //The link allows only the vendor card your on's, own vendor show info

That's if you want to be able to "enter" info per vendor (or customer with slight code change. change everything I said about Vendor to Customer).
You have to decide how you would like to handle it. Looking up orders with flowfiled per customer or having a table to log customers & shows.

oh! & to avoid duplicates set a useable key - our's is (Vendor No,Year,Type) perhaps your type would be "Event Code"

as far as

"Problem with that method is some customers do not place orders, or cancel an order. Yet we have a customer card open for them. We need to follow up with them."

Are you looking for a "Last Invoice Date" type of field? that you can filter on to see if it's been a while?

yuppicide

I probably never even noticed Relationship Management until reading this post... not something anyone has ever questioned. Unfortunately everything in there says I don't have permission.

Yes, the event codes are all different.. Surf Expo, for example has 2 shows a year Jan and Sept.. so SURF0110, SURF0810, then the same two, but with 11 at the end, and 12 at the end for years.

Event code doesn't print on any paperwork, but you can view it from Sales Order or Posted Invoices.

I've got an order to enter, then I'll read over the rest of your post and soak in the knowledge. Thanks for now.

yuppicide

Here is what I am thinking would be my easiest idea. It just requires one more step when someone is setting up a Customer Card.

On Table 18 - Customer I will add EventCode1,EventCode2, etc. I'll give them 5 maximum. There's no customer that really attends more than a few shows anyway.

Then on the Customer Card add a tab that says Shows. In there I'll have 5 text boxes that correspond to those 5 codes I just made on the table. People can type the show name and I'll be able to search by that.

That's easily doable.. would would be even nicer if I could get it to have a drop down box to make it take Event Codes from that table.. I can't get the drop down box to open.. maybe TableRelation needs something else.. it has "Event Code".Code so far.

Alex_Chow

Instead of making modifications, why not check out the Relationshipment management to see if it's to your liking?

I would stay away from creating new functions if NAV has something simliar.

Savatage

Alex Chow wrote:

Instead of making modifications, why not check out the Relationshipment management to see if it's to your liking?
I would stay away from creating new functions if NAV has something simliar.

From what I gather..Since they are on 3.1, I'm guessing that they have stopped paying any maintenance and thier license doesn't allow access to relationship management. So I'm guessing that's out of the question for them.

Setting up 5 fields in a customer card doesn't sound flexible enough for me.
What are you going to do clean it up each year?

Do you have a FREE Table to use? 50000 thru 50009? Where you can save customer #'s, year & show..then you can link it back to the customer card and it can maintain a show history per customer.

You can then even do a COUNT flowfield on the customer card called "Shows Attended" based on the "Show Table" you create.
Think about the big picture on how to handle this - 5 set fields on the customer card would be an absolute last resort (If i'm following you correctly)

yuppicide

Yes. Stopped paying maintenance.. it was costing a bundle of money each year and for what? Not much. That maintenance didn't really give us much.. if we required any modifications we'd still have to pay an hourly fee. If things we broken from the start and we just found out about it, they'd still charge us.. we were required to bring any problems to their attention within 90 days or something.. well that was like 10 or 11 years ago.. long before I was even here.

You're right about my idea, though.. it's not flexible at all and requires a lot of periodic cleaning. I was just thinking what I am capable of doing rather than what you are capable of teaching me I think.

Looking at tables.. 50000 - 50004 are in use.. so yes.. I do have 50005-50009 to use.

yuppicide

I think doing that is beyond my ability. I can make the Table for example 50007 Shows. I'll add No. Code 20 and Event Code Code 10.

But what to do after that is not something I know.

Savatage wrote:

Do you have a FREE Table to use? 50000 thru 50009? Where you can save customer #'s, year & show..then you can link it back to the customer card and it can maintain a show history per customer.

yuppicide

So in the way you are describing.. is this automatically populating from Posted Invoices and grabbing Event Codes? This won't totally work because we also need to see people who came to the show and didn't follow through with their order. Some people visit a show, we set up a customer card, then when it gets around the time to ship we'll call for Credit Card information and they say they have no funds available or whatever and the order gets canceled.

So, that's why I was also thinking just an area where we manually enter the data, by drilling down the box and selecting the appropriate Event Code.. and having it where you can setup 5 maximum.. I mean, a lot of stores are little boutiques or shops.. so it's not like they will travel from New York to Florida and vice versa. They might only attend the show in Florida or the show in New York.. maybe they attend one in Atlantic City or Virginia, but chances are they only go to one or two because it's too expensive to travel for a small boutique, so that's why I set 5 as the maximum.

Savatage

No this method isn't autopopulating anything - it was a way of saving show history in a different table per customer. This table can have an option field called "Attended,Did Not Attend".
Perhaps a combination of two fields (your call on what YOU need) defining if they showed interest or not and the second defining if they showed or not. Now you can see who showed interest but didn't show and all the other combo's.

This table has to be updated & maintained by someone. remember it doesn't hurt to build this and see if it's useful for you or not, else delete it all. If you want a flowfield lookup to show order then we can look at that too. but for this post I will focus on creating a customer show history table.

OK simply once you create your table & Key to avoid duplicates. ("no.","event code")

You will also create a form (Shows) based on that table (Shows) (I suggest Tabular Type Not Card Type) Add your fields.

So now you have a Table and a form.
Now you want to add a command button to your customer card.
view-> properies of the button
Caption = Show History
PushAction = RunObject
RunObject = Form Shows
RunFormLink = No.=FIELD(No.)

See if that works for you..

Save->Complie->Enjoy!

yuppicide

Thank you so much for the help. I ended up going a different route than you because of C/AL Global Keys I couldn't define due to license.

I went back to my original simpler method, which is not very good and can be full of human error.

I made a copy of Form 124 Comment Sheet and saved it as 50045 I think and named it something else.

I made a command button on my customer card which opens that.

I had to use "Table Name=CONST(Customer),No.=FIELD(No.)" for the RunFormLink otherwise the same comments would be true across all customers. This way it's different for every customer.

In there you can type date and comment. Comment is where you will enter the show name.

It would be better if instead of a comment field it would pull the from table 50000 Event Code which has data on form 50002 Event Code, but again C/AL Global restrictions don't allow me.

The worst part about this method is that it probably not going to be totally useful. In one way it will be useful because you can quickly see all shows a customer attended and won't have to view all the other customer comments. It won't be able to pull up all customers who visited a certain show. You can do a Table Filter, erase the customer name and put in *SURF* for example for comment.. but since I don't have C/AL Globals, I can't add anything to my form.. so I'm stuck with only Date and Comment. I can't add Name in there. So, it will only show dates and comments.. you'll not know which customer it was.

Savatage

yuppicide wrote:

I ended up going a different route than you because of C/AL Global Keys I couldn't define due to license.

Not sure I understand completely..
Most if not all I descibed can be accomplished in the new table.
By creating fields and the properties of those fields.
& view->keys to set a key

No global variables should be needed.