which DB columns contain what data?

[JuiceyBrucey]

Hi, I am doing queries and getting back a lot of data, but cannot find any reference to what each table column means.
For example with a transaction detail request from the SalesOrders table, i get:
["ItemNo"]=> int(1)
["CommentLine"]=> bool(false)
["QuantityOrdered"]=> float(1) // okay, seems straight forward
["QuantityDemand"]=> float(0) // okay, seems straight forward
["QuantityThisDel"]=> float(1) // okay, seems straight forward
["QuantityBackOrd"]=> float(0) // okay, seems straight forward
["Picked"]=> bool(false) // dunno
["PriceExGst"]=> float(3.2) // price contradicts UnitCost, does this mean price EX GST meaing price without GST?
["PriceIncGst"]=> float(3.3) // price contradicts UnitCost + TaxToCharge
["DiscountedPrice"]=> float(3) // is this the actual amount charged?
["TaxToCharge"]=> float(0.3) // 10% X UnitCost + Unit Cost != PriceIncGst
["UnitCost"]=> float(2.9)

Is there some sort of chart the gives explanations of what each column means so that I can use accurate numbers in my return display?
Cheers

[Mike.Sheen]

Is there some sort of chart the gives explanations of what each column means so that I can use accurate numbers in my return display?

The online help does have table definitions, but seems to be rather lacking in detail. You might want to approach our support team via the helpdesk and ask there (they don't use our forums).

["Picked"]=> bool(false) // dunno

Picked is a column in the table which corresponds to column on the screen named "Picked". It's a checkbox on the screen. If you cannot see that on-screen then it may be hidden, but you can unhide columns by right-clicking the column header, turning on "Use Custom Columns" and then right-clicking the column header again and selecting Manage Grid and un-hiding the column.

["PriceExGst"]=> float(3.2) // price contradicts UnitCost, does this mean price EX GST meaing price without GST?

Yes, PriceExGst means "Price ex GST" or "Price before Gst". Pretty common parlance in accounting.

["PriceIncGst"]=> float(3.3) // price contradicts UnitCost + TaxToCharge

Prices are sell related, costs are acquisition related - you should not confuse the two. UnitCost is how much it cost the seller per unit - the customer normally would never be exposed to this.

["DiscountedPrice"]=> float(3) // is this the actual amount charged?

Yes, Ex-GST.

["TaxToCharge"]=> float(0.3) // 10% X UnitCost + Unit Cost != PriceIncGst

TaxToCharge is always on the delivered total, not unit price and it's price not cost!. UnitCost isn't used to calculate GST, the sell price is - which is DiscountedPrice.

["UnitCost"]=> float(2.9)

This is the unit cost - how much it cost to acquire, not related to selling or GST.

We actually use Decimal(19,6) as the column type for all money and quantity columns. Floats are bad for currency or anything requiring accuracy.

[JuiceyBrucey]

Thank you Mike. Much appreciated.
That has cleared up a lot of questions.
Just a comment, the key value pairs mentioned in the post, came from a data dump after extraction.
So this:
["UnitCost"]=> float(2.9)
Is actually what came from the database.
According to this, the field is "float".
Just letting you know.

So would I be right in assuming most or all fields using the word "cost" are more than likely related to "cost of goods sold"?
Cheers and thank you.

[Mike.Sheen]

data dump after extraction.
So this:
["UnitCost"]=> float(2.9)
Is actually what came from the database.
According to this, the field is "float".
Just letting you know.

Well, whatever tool you used is wrong. SQL Server does have a float datatype, but it is different to the decimal type and using a float in accounting scenarios would be disasterous!

So would I be right in assuming most or all fields using the word "cost" are more than likely related to "cost of goods sold"?
Cheers and thank you.

Yes, absolutely correct.

[JuiceyBrucey]

this has been very helpful.
Thank you Mike.
This is resolved.

[JuiceyBrucey]

The SQL server lists decimals as decimal, but the JIWA table definitions list them as float.
weird. But no matter. As long as I know that the actual correct definition is always going to be on the SQL server, it should be fine.
thanks for your help Mike.
Cheers