Hep with a LIST_TABLE Statement - with query in WORD Doc

rocketman

Build Number	Database	OS
{3025}	{MySQL}	{Win Server 2019}

Can anyone help with the syntax of a LIST_TABLE Query. I’ve used them for years when I want the entire list but now I need to filter out some entries and can’t get a query to work.

These Work

<<LIST_TABLE(Members.Type,`Name`,`New_Annl_Fee`,`New_GB_Fee`,`New_Total`,`Revised_Total`)>>
<<LIST_TABLE(Members.Other_Site_Interests,`Site_ID`,`Site_Type`,`Glider`,`New_Annl_Fee`,`Revised_Total`)>>

This is a new one that requires a query. The query exists in the app and works fine when triggered from within the app, but when inserted into a Word doc, it returns nothing (not even an empty table)

<<LIST_TABLE(`FIND LLL_Query`,`SkillType`,`Date_Next_Check`,`License_Number`)>>

The subgroup is Members.SkillOfMember which is a kind of halfway house between the main Skills Table and the member (because it requires extra data that isn’t held in the Skills table because it is unique to each member ie Date_Next_Check

Individual Members are placed in context one at a time prior to calling the Word Document for the final merge. Members can have many Skills but in this instance I only want to show each member the details of their flight crew license type and the date of their next check flight.

The only other bit of info that might have a bearing is that “SkillType” is a shortcut
EDIT I only noticed when posting the apostrophies have been removed on my desktop PC (Google Chrome) They are not a standard apostrophe (UK Keyboard underneath the @ sign as in ', rather they are top left of the numeric row ` along with ¬, the standard comma has never worked for me

rocketman

Partial solution but not ideal.

Because this was an urgent requirement, I fudged this by putting the query in the last called process immediately prior to doing the WORD merge, so that the list I needed was in context and that worked. But it’s not ideal because now I’ve seen how it COULD work, it opens up all sorts of possibilities for my users if I could get the search query to work inside the LIST_TABLE statement.

Reading other comments from 2010, I’m guessing it’s going to be something to do with those pesky apostrophies because the filter query needs to be something like AND BO.SomeAttribute=‘some text string’

kklosson

I hope this helps. I send a daily email to investigators advising of their current workload. The message is in an HTML document.

Here’s the HTML:

This account is not monitored. Please do not reply.

Hello <<Investigator.FirstName>>,

You currently have <<Count PHS WHERE (PHS.Investigator=Investigator and PHS.Case_Stage<> '7 - Closed' AND PHS.Candidate.Status='Active')>> open investigations:
<<LIST_TABLE(`FIND PHS WHERE (PHS.Investigator=Investigator AND PHS.Case_Stage<> '7 - Closed' AND PHS.Candidate.Status='Active') ORDER By Case_DateOpened DESC`,`CandidateFullName`,`CandidateGroup`,`Case_DateOpened`,`Case_Stage`,`Case_ModifiedTimeStamp`)>>

You currently have <<Count Review WHERE(Review.Reviewer=Investigator AND (Review.Complete<>'Yes' OR Review.Complete IS UNDEFINED) AND Review.Results IS UNDEFINED) >> pending review assignments:
<<LIST_TABLE(`FIND Review WHERE Review.Reviewer=Investigator AND (Review.Complete<>'Yes' OR Review.Complete IS UNDEFINED) AND Review.Results IS UNDEFINED Order by Review.DateStarted`,`CandidateName`,`CandidateGroup`,`DateStarted`,`StartedBy`,`ReviewPurpose`,`NotesToReviewer`)>>

You currently have 
<<Count Task WHERE(Task.Investigator=Investigator AND Task.Status<>'Completed' AND Task.Status<>'Abandoned' AND Task.Status<>'Rejected' AND Task.DateInitiated IS DEFINED) >> pending tasks:

<<LIST_TABLE(`FIND Task WHERE(Task.Investigator=Investigator AND Task.Status<>'Completed' AND Task.Status<>'Abandoned' AND Task.Status<>'Rejected' AND Task.DateInitiated IS DEFINED) Order by Task.DateDue DESC`,`SC_CandidateFullName`,`TaskName`,`Status`,`InitiatedBy`,`DateDue`,`TaskDetails`)>>

Here’s the received message body:

nhofkes

From the description in your post, it’s difficult for me to understand the exact structure of your objects, but I’ll give you the approach that I used recently and worked well, including the conditional query.

Instead of LIST_TABLE, I use LIST_TABLE_START() and LIST_TABLE_END(), because this provides greater control over the formatting of the table in the Word document. This way I can define the table headers in the Word document and set the width of each column.

Furthermore, I put the query directly in the Word template itself, rather than referring to an existing query. So, in the Word table, direcly below the table header, I use something like:
<<LIST_TABLE_START('FIND ChildBO WHERE ChildBO IN ParentBO.Children AND (SomeOtherCondition)')>>

With this approach, it’s sufficient to put the relevant ParentBO in context for the mail merge, the query language in the LIST_TABLE_START function then executes the query for that object and you can use any condition that would also be valid when executed in the rule language.

Note that if you use LIST_TABLE_START(), you need to specify the required columns by the full BO name and attribute, i.e.
<<ChildBO.Attribute1>> <<ChildBO.Attribute2>>

After the last attribute, you need to include the <<LIST_TABLE_END()>> function.

rocketman

Thanks kklosson and nhofkes . Much food for thought. I’ll jump back into this after I’ve eaten. Will post results later.

rocketman

@kklosson and @nhofkes Thanks guys. all sorted. I think I made the mistake of assuming that because the original LIST Tables (minus any query - just the list) that the items would be in context already, but it seems maybe not. As soon as I put WHERE x in Y AND … it worked. Anyway for anyone following, here’s what my code looked like

Details of your main license Type

<<LIST_TABLE(`FIND MemberSkill WHERE MemberSkill IN Members.SkillsOfMem AND MemberSkill.SkillType='A. Licenses '`,`SkillType`,`Skill`,`Date_Next_Check`,`License_Number`)>>

Your License Endorsements

<<LIST_TABLE(`FIND MemberSkill WHERE MemberSkill IN Members.SkillsOfMem AND MemberSkill.SkillType='B. License Endorsements'`,`SkillType`,`Skill`,`Date_Next_Check`,`License_Number`)>>

Your Sporting Badges

<<LIST_TABLE(`FIND MemberSkill WHERE MemberSkill IN Members.SkillsOfMem AND MemberSkill.SkillType='C. Sporting Badges'`,`SkillType`,`Skill`,`Date_Next_Check`,`License_Number`)>>

And here’s what it looks like . I’ll be working on LIST_TABLE START and END next

nhofkes

Good to hear that it is solved.

I’ll be working on LIST_TABLE START and END next

If you are happy with the tables as they are, there is probably no need to change it. The only advantage, as far as I know, of using LIST_TABLE_START() and LIST_TABLE_END() is that you can define the table in Word including column widths etc. Note that if you use it, you must put each attribute in the relevant column in the Word document, for example:
| <<MemberSkill.SkillType>> | <<MemberSkill.Skill>> | <<MemberSkill.Date_Next_Check>> | <<MemberSkill.License_Number>> |