"Select distinct" in a LiveCode variable or field

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

Post Reply
cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

"Select distinct" in a LiveCode variable or field

Post by cbarbal » Wed Dec 19, 2018 4:23 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6265
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: "Select distinct" in a LiveCode variable or field

Post by dunbarx » Wed Dec 19, 2018 4:49 pm

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
Posts: 11317
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: "Select distinct" in a LiveCode variable or field

Post by Klaus » Wed Dec 19, 2018 5:02 pm

Hi Craig,
dunbarx wrote:
Wed Dec 19, 2018 4:49 pm
Not sure what "select distinct" is.
a quite helpful SQL command: https://www.w3schools.com/sql/sql_distinct.asp

Best

Klaus

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: "Select distinct" in a LiveCode variable or field

Post by cbarbal » Wed Dec 19, 2018 7:13 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6265
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: "Select distinct" in a LiveCode variable or field

Post by dunbarx » Wed Dec 19, 2018 8:13 pm

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
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: "Select distinct" in a LiveCode variable or field

Post by cbarbal » Thu Dec 20, 2018 5:46 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6265
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: "Select distinct" in a LiveCode variable or field

Post by dunbarx » Thu Dec 20, 2018 6:01 pm

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
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: "Select distinct" in a LiveCode variable or field

Post by cbarbal » Thu Dec 20, 2018 6:37 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6265
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: "Select distinct" in a LiveCode variable or field

Post by dunbarx » Thu Dec 20, 2018 6:41 pm

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

Craig

cbarbal
Posts: 114
Joined: Fri May 08, 2015 5:04 pm

Re: "Select distinct" in a LiveCode variable or field

Post by cbarbal » Thu Dec 20, 2018 7:06 pm

Hi,

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

Carles

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”