Date: prev next · Thread: first prev next last
2016 Archives by date, by thread · List index


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 <garydale@torfree.net> 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 <garydale@torfree.net <mailto:
garydale@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
         <garydale@torfree.net <mailto:garydale@torfree.net>> 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 have noted
             it. The document is
             simple, as is the spreadsheet and the base file that
             connects them.

             I tried changing the numbers to text but Base doesn't seem
             to allow that.
             It's decided that the columns for age and place are
             decimal numbers.

             So far as I can see, either the number comparisons operate
             differently
             from the text comparisons (which the documentation doesn't
             mention) or the
             number comparisons are broken. Normally I'd consider that
             unlikely, but my
             past experience with printing "form letters" and labels is
             that these areas
             don't get a lot of attention.


             On 27/04/16 03:32 PM, Bruce Hohl wrote:

                 Can you give more details of your set-up.  What is in
                 the Writer, Calc
                 and / or Base components of LO.

                 On Wed, Apr 27, 2016 at 11:44 AM, Gary Dale
                 <garydale@torfree.net <mailto:garydale@torfree.net>
                 <mailto:garydale@torfree.net
                 <mailto:garydale@torfree.net>>> wrote:

                      I'm trying to print certificates for a group of
                 athletes and need
                      to have them reflect their standing in the event.
                 The certificate
                      has a number of conditional text fields, such as
                 to translate
                      their gender from M/F to male/female, which are
                 working.

                      However the ones that are doing numeric
                 comparisons aren't. The
                      comparison always seems to return "true" so the
                 "then" condition
                      is printed. For example Run Results.Race.place ==
                 1 is the
                      condition but the translation text is always
                 "first place", never
                      empty. Since I have 5 different conditions (first
                 3 positions,
                      participate, supported), I get a lot of text
                 printed that shouldn't.

                      The slightly more complex Run Results.Race.age >
                 18 AND Run
                      Results.Race.age < 60 always prints adult. At one
                 point I had it
                      print an else (senior) but that branch was never
                 taken even when
                      the age was well past 60.

                      I've checked the Run Results.odb file (which
                 links to a
                      spreadsheet) and the age & place fields are both
                 of type decimal.

                      What am I doing wrong?

                      --     To unsubscribe e-mail to:
                 users+unsubscribe@global.libreoffice.org
                 <mailto:users%2Bunsubscribe@global.libreoffice.org>
                                     <mailto:
users%2Bunsubscribe@global.libreoffice.org
                 <mailto:users%252Bunsubscribe@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



             --
             To unsubscribe e-mail to:
             users+unsubscribe@global.libreoffice.org
             <mailto:users%2Bunsubscribe@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



     --     To unsubscribe e-mail to:
users+unsubscribe@global.libreoffice.org
     <mailto:users%2Bunsubscribe@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



--
To unsubscribe e-mail to: users+unsubscribe@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



--
To unsubscribe e-mail to: users+unsubscribe@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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.