"Select distinct" in a LiveCode variable or field

[cbarbal]

Hi all,

There is a query in SQL that returns me the expenses of a series of properties and places it in a variable, it can be a field if it goes better. Is it possible to obtain a single name of each property and the number of records of each one, of this variable or field? With the name it would be enough

The solution I have, which I do not know if it will penalize the report, is a Select Distinct to find the properties and then make as many queries as properties and include them in the report

Thanks in advance,

Carles

[dunbarx]

Hi.

Yes.

Not sure what "select distinct" is.

Give an example of the text returned by the query in order for us to help you identify and extract the pertinent data.

Craig Newman

[Klaus]

Hi Craig,

Not sure what "select distinct" is.

a quite helpful SQL command: https://www.w3schools.com/sql/sql_distinct.asp

Best

Klaus

[cbarbal]

Hi Craig,

This is part of the text. In the original there are 142 lines, there may be more or less.

Code: Select all

Aldea Mar	7875	2016-12-14	F-1	...
Aldea Mar	7876	2016-12-14	F-1	...
Llenguadoc 62	7790	2016-10-12	...
Llenguadoc 62	7812	2016-11-01	...
Llenguadoc 62
Llenguadoc 62
Llenguadoc 62
Olimpiades 37	7759	2016-10-03	...

The properties would be: Aldea Mar, Llenguadoc 62, Olimpiades 37. If we do itemDelimiter to tab, it would be the first item of each line.

Or I can make a "Select distinct" that returns the properties. Another "Select" with all the data of the report and through the "filter command" filter each property.

Regards,

Carles

[dunbarx]

OK.

I have never made an SQL query, so I cannot comment on the pros and cons of letting that protocol do its thing.

But in LC you have already answered your question. By parsing the data with delimiters (both "record" and "field" in spreadsheet parlance) you can deconstruct and reconstruct your data in any way you like.

Are there any particular concerns that you might have regarding the capabilities of LiveCode itself? The number of lines is not an issue.

Craig

[cbarbal]

Hi Craig,

In principle I will do with SQL, since the data is in a database, is related to the subject of the post https://forums.livecode.com/viewtopic.php?f=7&t=31904 . Now I am in the process of seeing how I can reproduce what I do in FileMaker in LiveCode and every day I learn new things. In an article by Richard Gaskin I saw the subject of Behaviors, it's great.

Then I will have the issue of exporting as Execel. In FM it's native, but with LC I think I'll have to use a plugin.

Regards,

Carles

[dunbarx]

Hi.

Then I will have the issue of exporting as Execel

It is not native, like creating a pdf file is, but it does not need to be. You can "write" to (and read from) any file, including an existing Excel file. Check out the "write to file" command in the dictionary.

Craig

[cbarbal]

Hi,

I understand that "write to file" is for an existing file, the difference is that FileMaker creates it for you. There are clients that need an Excel file because their administrative manager asks for it, and they do not have the application. I do not have it installed either and if I need to open an Excel file I do it with Numbers.

Carles

[dunbarx]

Anyone know if AppleScript can open a new Excel file, assuming the Excel application is resident, and name it?

Craig

[cbarbal]

Hi,

I suppose a possible solution is to have an empty Excel file and fill it out.

Carles