Need some thoughts on how best to display some datasets Topic is solved

[Keith-i]

Databases are not my forte (no pun intended) as I am pretty much self-taught but I know enough to get by most of the time. However, I am struggling to think of how best to interrogate my database and summarise some sets of statistical data that I need to do on an annual basis. On an earlier incarnation using MSAccess I use to run a query then copy the output to a spreadsheet to analyse the data, however I am sure there is a better way.

The data I need comes from two related tables, namely tblFees and tlbInstructions and will be based on records between a certain date range.

An example of what I need will have to include a WHERE FeeDate BETWEEN 'date-x' AND 'date-y' and then various AND clauses.
For the first dataset the AND clause would need to be AND tblFees.Lender is NOT '1' OR '37'

For the second dataset I need the same WHERE clause but this time AND tblInstructions.Level_1 is NOT null.

For the third dataset I need the same WHERE clause but this time AND tblInstructions.External is NOT null.

You get the gist I am sure, I have 9 of these scenarios to run.

Ultimately I need to get a row count from these datasets and SUM the tblFees.FeeAmount column. I am not interested in the individual rows.

Is this possible with iFrames or some other method where I can have all the queries on a single page? Any help or pointers gratefully received.

Thanks

[kev1n]

Hi,

You could use a Launch form with Display objects that run these individual SQL statements.

[Keith-i]

Thanks kev1n, that looks promising. Is there any way to format the figure that gets displayed in the display box? I'd like currency if possible but I can't see any option on the properties dialogue box that allows format.

[kev1n]

To format currency values in the UK format, you typically use the pound sterling (£) symbol as the currency symbol and separate thousands with commas and use a period as the decimal separator. You can achieve this formatting in MySQL using the `CONCAT` function to add the currency symbol and the `FORMAT` function to format the number with commas and decimal places. Here's an example:

Code: Select all

SELECT CONCAT('£', FORMAT(your_numeric_value, 2)) AS uk_currency;

Replace `your_numeric_value` with the actual numeric value you want to format as UK currency. The `FORMAT` function formats the numeric value with two decimal places and commas as thousands separators, and the `CONCAT` function adds the pound symbol (£) before the formatted value.

For example, if you want to format the numeric value 1234567.89 as UK currency, you would use the following SQL query:

Code: Select all

SELECT CONCAT('£', FORMAT(1234567.89, 2)) AS uk_currency;

This query would return the formatted UK currency value as a string: "£1,234,567.89".

[Keith-i]

Thanks for the info. I'm already using SELECT SUM(netfee) in my SQL so adding formatting is probably going to confuse me and not worth the effort at the moment.

However, another query has arisen. I have a couple of filter boxes on my Launch form for entering a date range. How do I get the Display boxes to update after I've entered the second date? I can manually refresh the screen which then updates the figures in teh display boxes but it also clears the dates I've entered in the date boxes.

[kev1n]

To refresh a Display object, call nuRefreshDisplayObject().

To refresh all Display objects on a form, add an onchange event to the date fields and call refreshDisplayObjects():

Code: Select all

function refreshDisplayObjects() {
	const obj = nuSERVERRESPONSE.objects;
	for (let i = 0; i < obj.length; i++) {
		const oType = obj[i].type;
		if (oType === 'display') {
			nuRefreshDisplayObject(obj[i].id);
		}
	}
}

I would also add a check to see if a "Date from" and a "Date to" have been entered.

To apply formatting to a SUM():

Code: Select all

SELECT CONCAT('£', FORMAT(SUM(netfee), 2))  FROM `your_table`

[Keith-i]

Thanks so much for your help.

I now have the formatting sorted, although in case anyone follows this in the future there was one too many closing braces. The working version is:

Code: Select all

SELECT CONCAT('£', FORMAT(SUM(NetFee), 2))

However, I can't seem to get the refresh to work. I may be missing something but have pasted your sample code into an onchange event in custom code on the second date box but nothing happens after selecting a date.

[kev1n]

The function refreshDisplayObjects() {....} itself can be pasted in Custom Code.

Then, in the onchange event, call it like this:

Code: Select all

refreshDisplayObjects();

[Keith-i]

Still having some trouble getting anything to happen.

Just to clarify I have created an onchange event under custom code for my 'date_to' object.

Code: Select all

refreshDisplayObjects();

I have also added the following to the custom code of the main Launch form properties.

Code: Select all

function refreshDisplayObjects() {
	const obj = nuSERVERRESPONSE.objects;
	for (let i = 0; i < obj.length; i++) {
		const oType = obj[i].type;
		if (oType === 'display') {
			nuRefreshDisplayObject(obj[i].id);
		}
	}
}

[kev1n]

Any errors? And what's your nuBuilder version?

If you encounter an issue, be sure to check the developer console for errors by clicking the `F12` key on most browsers.