Who knows SQL and can help me? I am running a program that tracks fleet vehicles, part of it is billing that uses SQL. I need to show a tax component in my invoices that has the GST included amount. So what I need to do is have a field that shows a figure of 9.09% of another field. I know where the SQL function needs to go, but I am having difficulty in writing the correct function so it works.
Well at the moment I'm writing Java and SQL programs that deal with electricity meter billing. Perhaps i could help. What are you writing the program in?
SELECT <<price_col>> * X as `gst`, <<price_col>> as `price` FROM <<whatever table>> price_col is the name of the colum that contains your price...... X is the number by which you want to multiply the price in order to get the GST value whatever table is the name of the table you're getting this data from
It's not really a table that i need to get the information from. It is SQL embedded in the program I think. Here is a screen shot of the SQL part and the bit i need to change.
The txtTax is the bit that the operation in the lower pane describes, at the moment it just reads $0.00. what I need to do is make it show a value calculated from another field. The other field name is DBText15.
I don't think that would work anyway, because I need to calculate the value from another field in the same document. You need to see the program I think to see how it works. If anyone wants to download the trial version and look at it, it's called Fleet Maintenance Pro.
still want help with this? downloaded that program but have no idea how to get to the screen in the picture above
You need to create an invoice first. On the tool bar there is 'Billing', click that, then 'generate invoice'. Click the 'misc items' tab and click add. Type something there and put in a price, doesn't matter what. Make the quantity 1. Then click save. There is a check box that says print upon saving, check that and click save. A 'Report Filtering Options' screen appears, click the 'customize' button bottom left. It will bring up the 'Maintenance Report Designer'. Top left is a 'calc' tab, click that. On the menu in the left pane, scroll down until you see 'txtTax', it will be highlighted in green, that shows that there are event handlers for that option. Click on it and in the right pane will appear the 'OnGetText' with a green page next to it, click that and you will see the SQL statement. Bit of messing around I know.
Thats not sql. Whats the actual program you're running, it looks like a derivative of PASCAL to extrapolate the data. It would be something like Begin if Text = '' then Text = '$0.00'; var temp : Float; temp = (StrToFloat(Text) * 0.0909); End temp would be where the result is stored. I haven't used delphi/pascal for 10 years now, and I can't remember how StrToFloat handles the $ symbol.
I've been in contact with their support and they tell me I need to know SQL to be able to do what I need it to do. Fleet Maintenance Pro is the program. After looking at it a bit more, it looks like each part of the invoice is a separate table.
What you're toying around with isn't sql though. Thats real code, to help render the information or process the information once it's already been retrieved from the table. If you need more information from the tables, there must be somewhere else that you can edit the sql statements. Refer to my edited post above for a likely close solution.
There is a data tab as well, it shows how the data is handled and where it is stored, but there is no place to write and save new code. Screen shot of that.
Just did a bit of research it seems to use the ODBC connector, which no doubt means it's using a msaccess database in the backend, so all queries are actually stored inside the database, then probably defined and called somehow within the application elsewhere. You should be able to access the database file in the root of the application folder, or somewhere in that folder. It will have the extension MDB, you should be able to double click it and it'll open access (provided you have office professional). You should be able to create a query in the design view, just finding the way the program accesses that information is the tricky bit. Without it infront of me, there isn't really much else I can help you with. That seems like the most logical pathway that the program works however.
Can't access anything through the root folder, there are a heap of IDX and BPL files. In the Data screen, I can right-click the pane and 'edit sql' is an option, but when I click it, all it does is allow me to change what is on the screen, not really any of the actual code. In the design view I can change text fields and add images but not any of the data fields. It's only when I generate an invoice that isn't attached to a workorder though, there is a tax value field for workorders but not in invoicing, a bit of an oversight from the developers I think, they mustn't have allowed parts to be charged that aren't attached to a vehicle. Maybe I can create a bogus vehicle just so I can charge miscellaneous parts out. I'm waiting on a reply from the support guy, but as he is in the US it's two days turn around. Screenshot of the edit sql screen.
OK, found another part that is editable, but I still don't know the correct statement to make it work.
It gets more interesting, after a bit of googling, apparently it is Delphi, doing a bit of a crash course now, hopefully I can figure it out.
SQL for Tax component I realise this thread is now very old - but I think this is the script that they were after: PRL Software - Tax Component It calculates the GST component based on the invoice total (or invoiced lines if you want).
Thanks for that. I can find out what to do, but it is the where to do it that is the problem. Everything I try doesn't seem to work. Borland Delphi is the program, I have a book here, but it goes through different concepts.
