which DB columns contain what data?  Topic is solved

Support for Microsoft SQL Server in the context of Jiwa installations.

which DB columns contain what data?

Postby JuiceyBrucey » Thu Feb 25, 2021 9:54 am

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
JuiceyBrucey
Frequent Contributor
Frequent Contributor
 
Posts: 151
Joined: Tue Aug 18, 2020 7:19 pm
Topics Solved: 1

Re: which DB columns contain what data?  Topic is solved

Postby Mike.Sheen » Thu Feb 25, 2021 11:33 am

JuiceyBrucey wrote: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).

JuiceyBrucey wrote:["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.

JuiceyBrucey wrote:["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.

JuiceyBrucey wrote:["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.

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

Yes, Ex-GST.

JuiceyBrucey wrote:["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.

JuiceyBrucey wrote:["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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2497
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 770

Re: which DB columns contain what data?

Postby JuiceyBrucey » Thu Feb 25, 2021 12:35 pm

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.
JuiceyBrucey
Frequent Contributor
Frequent Contributor
 
Posts: 151
Joined: Tue Aug 18, 2020 7:19 pm
Topics Solved: 1

Re: which DB columns contain what data?

Postby Mike.Sheen » Thu Feb 25, 2021 12:54 pm

JuiceyBrucey wrote: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!

JuiceyBrucey wrote: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.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2497
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 770

Re: which DB columns contain what data?

Postby JuiceyBrucey » Fri Feb 26, 2021 4:45 pm

this has been very helpful.
Thank you Mike.
This is resolved.
JuiceyBrucey
Frequent Contributor
Frequent Contributor
 
Posts: 151
Joined: Tue Aug 18, 2020 7:19 pm
Topics Solved: 1

Re: which DB columns contain what data?

Postby JuiceyBrucey » Fri Feb 26, 2021 5:08 pm

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
JuiceyBrucey
Frequent Contributor
Frequent Contributor
 
Posts: 151
Joined: Tue Aug 18, 2020 7:19 pm
Topics Solved: 1


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest