SQL Query

santhanak

hi everyone,

we wanted to find out the entry type in sql query, when we run the query the values are comin in o,1,2,3,4,5, but when we go to the item ledger entry table we can able to see only four options ie purchase,sales,postive adjustment & negative adjustment. now how can we match to which one? 0 = ? 1=? 2=? 3=? 4=? 5=?

the same case for costing method toooo if we run the sql query it runs in some values 1,2,3,4,5.... here we dont need this values but we need the perfect description/name..



the next one is how to get the Unit cost (ACY) in the sql query. we have tried to run from the value entry table but the tracsaction or more.....
for eg. we need the report as below

item no qty unit cost unit cost(ACY) total value total value (ACY)

thanks

canadian_bacon

For Item Ledger Entry, the "Entry Type" field is actually:
Purchase,Sale,Positive Adjmt.,Negative Adjmt.,Transfer,Consumption,Output
It is a 0-based index, meaning that 0=Purchase, 1=Sale, etc.
Go to the object designer and go into design mode for the table. In the properties window for the field look at the OptionString property for the values.

Now in terms of getting the flowfield values, you will have to look into the appropriate Flowfield tables to get this value. You can find this info by doing a SQL trace on a CALCFIELDS statement in NAV.

kine

1) You can create "mapping" table, which you can fill by the number and text values and "translate" the values through it. This table can be filled in from within NAV through RecordRef and FieldRef (there is OptionString function).
2) You can hardcode the values (not nice)
3) You can use e.g. Webservice to read the values (it depends on usage)

This was solved in other threads. You can try to find them.