Re: [libreoffice-users] Number comparisons in conditional text
The final query isn't too bad. Unfortunately Base doesn't like pretty-printing so a raw view is hard to read. Here's what I finally used. I just need to copy the raw spreadsheet into a Base table then print against the query. It still means the code is split across two documents (the certificate and the database) since I still need conditional text to insert a middle initial and it's still simpler to handle gender in the document. SELECT "event_name", "first_name", "middle_initial", "last_name", "sex", "age", "email", "place", CASE WHEN "age" BETWEEN 0 AND 18 THEN 'Youth' WHEN "age" BETWEEN 19 AND 59 THEN 'Adult' ELSE 'Senior' END "Group", CASE WHEN "place" = 0 THEN 'supporting' WHEN "place" = 1 THEN 'first' WHEN "place" = 2 THEN 'second' WHEN "place" = 3 THEN 'third' WHEN "place" >= 4 THEN 'participating' END "Finish" FROM "Results" On 09/05/16 08:24 AM, Bruce Hohl wrote: A (more difficult) method to use the age ranges from their own table (separate from the other data) would be to use a correlated subquery. Then you could have "cleaner" SQL. You can find examples of this on the web. OR I think there may be limited support for saving query formatting like discussed here: https://forum.openoffice.org/en/forum/viewtopic.php?f=61=39997 but I don't have any experience with how this works or its limitations. On Mon, May 9, 2016 at 12:00 AM, Gary Dalewrote: Thanks Bruce. I appreciate the help. I was trying values from a table that translates age ranges into text. I hate hard coding numbers, let alone the number of ranges, because I handle events for two different groups who have their own definitions. They used to be encoded in the different certificates... My sql was SELECT "results".*, "Categories".* FROM { oj "results" LEFT OUTER JOIN "Categories" ON "results"."age" BETWEEN "Categories"."low" AND "Categories"."high" } which doesn't work but does work when I use "results"."age" = "Categories"."low", leading to my complaint that BETWEEN doesn't work. Your use of a CASE statement with just the one table looks like it will do the trick, even if the code will be ugly. I'll need 2 different CASE statements with a total of 8 different cases to do the translations. On 08/05/16 09:43 PM, Bruce Hohl wrote: Gary, See attached for a working case when in LO Base. You do have to be very careful when using Base Queries (SQL) that everything is exactly correct like where commas are placed (or not) and how quotes / half quotes are used (or not). So setting this up in Base will likely take more time than in Calc. You will have to judge if the final result in Base is best for your needs. Good Luck! On Sun, May 8, 2016 at 4:31 PM, Gary Dale wrote: The first option is what I have been doing - it uses a calc file to drive a mailing list. The logic is in the document, not in the calc file, so that I can bring in new data without a lot of effort - the raw data can just be a .csv file. The second option, using a "smart" calc file, moves the logic to the data file which is what I don't want to do. It means importing the data or calculation into a calc file each time. The second option, moving the logic into a base file seems more promising. However, after much wailing and gnashing of teeth, I have to question whether base can do what I want. I can't get it to take a query using a BETWEEN criterion (e.g., youth is AGE between 0 AND 18). It won't (apparently) do any queries on calc sheets. Fortunately copy/pasting to create a new base table is easy enough. Unfortunately without being able to match the main table (containing participant information) against age categories, etc., it's not much good. I suppose I can use a brute force technique and create a table row for every possible age (for example) but that seems like a ludicrous kludge when the basic problem is that Writer's form letter functions can't compare numbers. On 28/04/16 09:58 PM, Bruce Hohl wrote: Two more ideas for this problem: (1) Do not use a Calc file - see LO_Labels_howto.odt, (2) Move the logic from Writer into either Calc or a Base query. While I can't explain how this might work for your Certificate mail merge I can show you through the attached examples how I used these two ideas to solve my mail merge problem. Good Luck and I hope you can make this work for you in LO. On Wed, Apr 27, 2016 at 9:17 PM, Gary Dale wrote: I can browse to a spreadsheet directly without first attaching it to a database, but that's just a kludge. LibreOffice creates the base file when you select the spreadsheet, which brings me back to the same problem. On 27/04/16 07:24 PM, Bruce Hohl wrote: Reportedly, as of LO 5.1 you can create a mail merge without a Base file - reference this:
Re: [libreoffice-users] Number comparisons in conditional text
A (more difficult) method to use the age ranges from their own table (separate from the other data) would be to use a correlated subquery. Then you could have "cleaner" SQL. You can find examples of this on the web. OR I think there may be limited support for saving query formatting like discussed here: https://forum.openoffice.org/en/forum/viewtopic.php?f=61=39997 but I don't have any experience with how this works or its limitations. On Mon, May 9, 2016 at 12:00 AM, Gary Dalewrote: > Thanks Bruce. I appreciate the help. I was trying values from a table that > translates age ranges into text. I hate hard coding numbers, let alone the > number of ranges, because I handle events for two different groups who have > their own definitions. They used to be encoded in the different > certificates... > > My sql was > > SELECT "results".*, "Categories".* FROM { oj "results" LEFT OUTER JOIN > "Categories" ON "results"."age" BETWEEN "Categories"."low" AND > "Categories"."high" } > > which doesn't work but does work when I use "results"."age" = > "Categories"."low", leading to my complaint that BETWEEN doesn't work. > > Your use of a CASE statement with just the one table looks like it will do > the trick, even if the code will be ugly. I'll need 2 different CASE > statements with a total of 8 different cases to do the translations. > > > > On 08/05/16 09:43 PM, Bruce Hohl wrote: > >> Gary, >> See attached for a working case when in LO Base. You do have to be very >> careful when using Base Queries (SQL) that everything is exactly correct >> like where commas are placed (or not) and how quotes / half quotes are >> used >> (or not). So setting this up in Base will likely take more time than in >> Calc. You will have to judge if the final result in Base is best for your >> needs. Good Luck! >> >> On Sun, May 8, 2016 at 4:31 PM, Gary Dale wrote: >> >> The first option is what I have been doing - it uses a calc file to drive >>> a mailing list. The logic is in the document, not in the calc file, so >>> that >>> I can bring in new data without a lot of effort - the raw data can just >>> be >>> a .csv file. >>> >>> The second option, using a "smart" calc file, moves the logic to the data >>> file which is what I don't want to do. It means importing the data or >>> calculation into a calc file each time. The second option, moving the >>> logic >>> into a base file seems more promising. >>> >>> However, after much wailing and gnashing of teeth, I have to question >>> whether base can do what I want. I can't get it to take a query using a >>> BETWEEN criterion (e.g., youth is AGE between 0 AND 18). >>> >>> It won't (apparently) do any queries on calc sheets. Fortunately >>> copy/pasting to create a new base table is easy enough. Unfortunately >>> without being able to match the main table (containing participant >>> information) against age categories, etc., it's not much good. >>> >>> I suppose I can use a brute force technique and create a table row for >>> every possible age (for example) but that seems like a ludicrous kludge >>> when the basic problem is that Writer's form letter functions can't >>> compare >>> numbers. >>> >>> >>> On 28/04/16 09:58 PM, Bruce Hohl wrote: >>> >>> Two more ideas for this problem: (1) Do not use a Calc file - see LO_Labels_howto.odt, (2) Move the logic from Writer into either Calc or a Base query. While I can't explain how this might work for your Certificate mail merge I can show you through the attached examples how I used these two ideas to solve my mail merge problem. Good Luck and I hope you can make this work for you in LO. On Wed, Apr 27, 2016 at 9:17 PM, Gary Dale >> garyd...@torfree.net>> wrote: I can browse to a spreadsheet directly without first attaching it to a database, but that's just a kludge. LibreOffice creates the base file when you select the spreadsheet, which brings me back to the same problem. On 27/04/16 07:24 PM, Bruce Hohl wrote: Reportedly, as of LO 5.1 you can create a mail merge without a Base file - reference this: http://vmiklos.hu/blog/mail-merge-embedding.html You might try to recreate your Certificate mail merge without the Base file being careful to select cell formats for your data that work with Writer. (If you have good spreadsheet skills you could likely get the job done entirely in Calc.) On Wed, Apr 27, 2016 at 6:18 PM, Gary Dale > wrote: I've tried it with Debian/Stretch v5.1.2.2.0+ and Windows 7 v5.1.3. If I was running anything old or unusual, I would
[libreoffice-users] Re: Inserting user first and last name into a cell in Calc
Le 07/05/2016 16:15, John Meyer a écrit : Hi John, > I'm trying to find a way to insert a user's first and last name as defined > in the user date into a cell in a spreadsheet. Is there a function that > will let me do that? Probably with a macro that fetches that information : https://forum.openoffice.org/en/forum/viewtopic.php?f=20=14750 which you would then have to insert into the cell in question via another macro routine. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted