Hello
I whant to calculate how many items were sold by each seller (example in august).Does this table exist in sql database? Where is saved who sold item?
That for is SalesPerson code in SO/SI.
You may assign these codes to every user, and Navision will fill it in automatically then. There is even a standart report to show each SalesPerson activities, and std Navision offers calculating commissions for them.
I have report wich shows sum of sold items wich sold seller, but this is sum and i whant to get data with date period, where will be wich items he sold and price of item. I tryed to find it in “Pos Transaction”, but found nothing.
if you assign the salesperson code, you can have a report that will show the items sold per salesperson in the customer/item sales
try also the dimension functionalities of NAV.
What is SO/SII ?
I whant to get these data in sql. I think that Tables have relation each other. I only found this table “POS Transaction” and it has “Receipt No_” column
Sales Order/Sales invoice.
You obviously need to understand the table structure and the processes before doing anything in SQL.
Sales Order / Sales Invoice [:D]
but SalesPerson Code, if set for user, is added whereas possible and logical, not only in theses 2 docs.
Your main table of interest is ILE (Item Ledger Entry, get accustomed tho these abbreviations, here in forum everybody is too lazy to write full names of common objects, me included [;)] )Then, ather tables might be needed, link is done usually by EntryNo, TransactionNo, DocNo etc etc fields. When you get familiar with Navision table structure, it is easy.
Guessing your possible next question - no, there is NO Entity relationship schema avaliable for Navision DB as single doc, never has been. Only generic schema of one AppArea table is given in Nav Architecture guide, search for it in Downloads section.
OK thank you.
I looked Sales Order / Sales Invoice form and it uses “Sales Line” table, but when i saw this table there are only 1264 records. After that i checked “Customer ledger entry” table but “Salesperson Code” filed is empty.
How is you POS getting into NAV? Does it map to the sales order table? Does it include the salesperson?
If you manually add an order then you can add the salesperson, it will appear on the order. I cannot check the Cust Ledger Entry table as I do not have NAV but if it is in the table and empty it means you never populated it.
When seller sells item, he does following steps:
1)Opens “LS RETAIL-POS”
2)Opens “Pos Client”
3)Logins
4)After that chooses sales person from “Staff” list then
5)Chooses client
7)Chooses item
8)After that choses payment metod
I can not understand where does this data goes. if “sales person code” field is empty
in “Customer ledger entry” then why they need to choose seller from staf list?
And there is one table in “Sales history”, wich shows sum total of sold items about each seller with date period.
Not knowing the add-on it is difficult to say, but my guess is you are not stating the salesperson, or the mapping into the sales order is not taking the “staff” entry and defining it as the salesperson, so it is an add-on mapping issue or a processing issue.
I suggest you load a sales order, look for it in NAV, see where the “Staff” entry goes in the standard order, whilst verifying the standard salesperson field is empty.
I cannot answer why you choose the seller, it would make sense it goes into the salesperson, but this depends upon the add-on and the mapping and possibly the configuration.
Nav offers a very good solution for that. Please follow these steps and check if this will solve your problem (in a test enviroment):
- Insert in item card, in tab General the code of Sales Person. (if it doesn’t exist you have to create them).
- Sell this Item to a customer
- Go to menu Sales & Marketing → Sales → Reports → Salespeople/teams and select the report "Salesperson Sales Statistic"
- You will see this report:
TEST
All amounts are in LCY
Code Sales (LCY) Profit (LCY) Adjusted Profit (LCY) Profit % Adjusted Profit % Invoice Disc. Amount (LCY) Payment Disc. Given (LCY) Pmt. Tolerance (LCY)
AL.FR 89,300.00 -5,200.00 -5,200.00 -5.8 -5.8 0 0 0
FRN 0 0 0 0 0 0 0 0
I-GJK 3,003,442.45 312,138.63 312,138.63 10.4 10.4 0 0 0
I-IO 3,790,736.02 117,566.42 117,566.42 3.1 3.1 0 0 0
I-ISP 0 0 0 0 0 0 0 0
I-ISS 3,393,812.85 47,565.59 47,565.59 1.4 1.4 0 0 0
I-IST 6,013,917.64 748,093.66 748,093.66 12.4 12.4 0 0 0
I-IST D 8,100,278.02 956,079.82 956,079.82 11.8 11.8 0 0 0
I-ITD 32,023.37 348.02 348.02 1.1 1.1 0 0 0
K-TJ 0 0 0 0 0 0 0 0
R-ABA 10,221,822.84 2,000,153.09 2,000,153.09 19.6 19.6 0
Black Parade
I have report wich shows total sum of sold items, but in this acse i can not determine how many wich item was sold by seller.
My navision version is 4. I found "Sales Statistics" but i could not find sold items there.
My mission is to make reiting between sellers. Count how many item was sold in one month by each seller and determine best seller who sold more items in one months then others. To get this data in sql and after that export it in excel is very easy
but my problem is that i do not know how to find this data in sql.
I don’t know if you have a schema or diagram of your database but please have a look at this file:
http://www.navitools.com/samplesER.html
In the file “contact management” i think you will find some useful information about tables.
Hope it helps,
Regards.
Only relevant when we find out what the add-on does. If it does not push the staff into the sales order then it all seems a bit irrelevant. They are not entering a NAV sales order, but pushing it in, so depending upon the mapping depends upon if the data is actually in the system to base a report upon, given the information do far I would guess the add on does not work in this manner and a salesperson has never been captured, or it is a completely separate field to the standard salesperson and does not reside at a transactional level.
FYI,
In the table that i’ve posted, the first column that has the name “code”, that column rapresent the code of the sales person. Which means that, the report shows you which sales person has done more sales and which one has done less than the other.
I’m writing this because i see that maybe i have not clarified that information.
Regards.
No I think it was pretty clear, but if the add-on never populates the salesperson then that will only ever be a blank field and the split/analysis cannot be achieved.
I ask this question to our partner’s developer and he said something like that this information must be
in “Transaction line” or “Pos Transaction line”. This table is like “Transaction Header” i found “Transaction Header”, but i can not find “Transaction line”, the problem is that i do not know exactly name of this table.
I think that the information you need is in tables, “sales Header” and “Sales Line”.
In sales header you will find a row named Purchaser code, also in this table you have information about Amount. But you need QTY, so i sugest to create a match between 2 tables, specify key order No, and take off all quotes (select only sales, not also pending sales).
If you do that you’ll have the list with purchasers and qty sold and amounts. Sort and that’s it, you got it. Hope your version has what i mentioned.
Regards.
IF this was standard Navision the response from Black Parade would be correct.
HOWEVER this is LS-RETAIL. The developer is, I beleive, pointing you to the tables it interfaces with. If your “Staff” entry is in the transaction header then this will help your report. Get the tablename from teh developer, and whilst at it ask them to confirm where the field “Staff” gets passed in Navision and whether or not it ends up in the standard Sales Header table.