Storing and Recalling Inline Database Results

This article discusses how database results which are stored in an array can be fed back into an inline so all the familiar [Records] ... [/Records] and [Field] tags work on them.

Introduction

The [Records_Array] tag provides an array of database results from any Lasso inline. This data structure can be used to quickly output the results of a database query or as the input for algorithmic processing of the database results. (The [Records] ... [/Records] and [Field] tags are preferred for the display of database results.)

The [Records_Array] tag allows database results to be filtered and sorted by Lasso, but how do you then display those results? Ideally, we'd like to use familiar [Records] ... [/Records] and [Field] tags to display the filtered and sorted results so the same display code can be used with a normal inline or with our post-processed inline results.

This article shows how the tags which are used to build custom data sources can be used to feed [Records_Array] results back into an inline so all the normal tags for displaying database results work. First, we'll show how the tags work on a page, then we'll shown how they are implemented.

Storing and Recalling an Inline

This tip implements two tags. The first is [Inline_Store] which is called within an inline whose results we want to capture. This tag returns a map that contains all the results of the inline and additional information required to recreate the inline. The second is [Inline_Recall] ... [/Inline_Recall]. This container tag accepts a map as input. Within the container any of the normal tags to return database results [Field], [Link_...], etc. can be used.

The [Inline_Store] tag is used by calling the tag within an inline and storing its value in a variable. This variable will be used later to recall the stored inline values. For example, the following code stores the result of a -FindAll inline for the People table in the variable $myInline.

[Inline: -Database='Contacts', -Table='People', -FindAll]

  [Var: 'myInline' = Inline_Store]

[/Inline]

 

The [Inline_Recall] tag can be called later with the $myInline variable. Within this container the [Records] ... [/Records] and [Field] tags are used just as they would be within the normal inline. They return the results that were stored for the inline further up the page.

[Inline_Recall: $myInline]

    [Records]

        [Field: 'First'] [Field: 'Last'] [Field: 'Age']

    [/Records]

[/Inline_Recall]

 

John Doe 21
Jane Doe 22
Bob Surname 45
Tia Lastname 35

How It Works

The code for the [Inline_Store] tag is included below, but a quick description of how it works is necessary for the remaining examples.

[Inline_Store] simply creates a map which includes elements for 'records_array', 'field_names', 'database_name', 'table_name', etc. If we call [$myInline->(find: 'records_array')] we'll get the value of [Records_Array] from the inline. If we call [$myInline->(find: 'field_names')] we'll get the value of [Field_Names] from the inline. And, [$myInline->(find: 'found_count')] returns the [Found_Count] for the inline.

Filtering and Sorting Results

We can apply the code from the previous two tips of the week to the values generated by [Inline_Store] and stored in $myInline.

For example, we can adapt the code which found all people whose age was between 30 and 50 as follows. Rather than using $myFieldNames we use $myInline->(find: 'field_names') and rather than using $myRecordsArray we use $myInline->(find: 'records_array'). Finally, after we generate $mySubQuery we store it back into $myInline replacing the value for 'records_array' and we adjust the stored 'found_count' to reflect how many records are now in the inline.

 [Var: 'AgeField' = $myInline->(Find: 'field_names')->(FindPosition: 'Age')->First]

[Var: 'mySubQuery' =  $myInline->(Find: 'records_array')->(Find:(Ex_Match_Element: $AgeField, (Match_Range: 30,50)))]

[$myInline->(insert: 'records_array' = $mySubQuery)]

[$myInline->(insert: 'found_count' = $mySubQuery->size)]

 

Outputting the results using [Inline_Recall] ... [/Inline_Recall] we see only the two older people from our database.

 [Inline_Recall: $myInline]

    [Records]

        [Field: 'First'] [Field: 'Last'] [Field: 'Age']

    [/Records]

[/Inline_Recall]

 

Bob Surname 45
Tia Lastname 35

Now, we can adapt the code for sorting the found set by the Last name field as follows. We again replace $myFieldNames with $myInline->(Find: 'field_names') and $myRecordsArray with $myInline->(find: 'records_array'). However, we manipulating the stored records array in place and the found count isn't changing so there is no need to modify the stored found count.

[Var: 'LastField' = $myInline->(Find: 'field_names')->(FindPosition: 'Last')->First]

[$myInline->(Find: 'records_array')->(SortWith: (Ex_Compare_Element: $LastField))]

Outputting the results using [Inline_Recall] ... [/Inline_Recall] we see records are now sorted in alphabetical order by last name.

 [Inline_Recall: $myInline]

    [Records]

        [Field: 'First'] [Field: 'Last'] [Field: 'Age']

    [/Records]

[/Inline]

 

Bob Surname 45
Tia Lastname 35

Implementation

The implementation of [Inline_Store] is simple. It returns a map which stores the values for all the tags which return information about the current inline. We store the [Records_Array], [Field_Names], and [Found_Count] to recreate the found set. The rest of the values are used to recreate the environment of the inline as closely as possible within the [Inline_Recall] ... [/Inline_Recall] tags.

!Insert Lassoscript here

Define_Tag: 'inline_store';

    Return: (Map:

    'records_array' =  <a href="http://www.lassotech.com/mailtoRecords_Array">@Records_Array</a>,

    'field_names' = <a href="http://www.lassotech.com/mailtoField_Names">@Field_Names</a>,

    'found_count' = <a href="http://www.lassotech.com/mailtoFound_Count">@Found_Count</a>,

    'skiprecords_value' = <a href="http://www.lassotech.com/mailtoSkiprecords_Value">@Skiprecords_Value</a>,

    'maxrecords_value' = <a href="http://www.lassotech.com/mailtoMaxrecords_Value">@Maxrecords_Value</a>,

    'database_name' = <a href="http://www.lassotech.com/mailtoDatabase_Name">@Database_Name</a>,

    'table_name' = <a href="http://www.lassotech.com/mailtoTable_Name">@Table_Name</a>,

    'keyfield_name' = <a href="http://www.lassotech.com/mailtoKeyField_Name">@KeyField_Name</a>,

    'schema_name' = <a href="http://www.lassotech.com/mailtoSchema_Name">@Schema_Name</a>,

    'lasso_currentaction' = <a href="http://www.lassotech.com/mailtoLasso_CurrentAction">@Lasso_CurrentAction);</a>

/Define_Tag;

?>

 

The implementation of the [Inline_Recall] ... [/Inline_Recall] tags is a bit more esoteric. The tag uses two techniques to recreate the environment of the inline.

First, an actual inline with -StatementOnly is used to restore the database name, table name, max records and skip records values, and action. The -StatementOnly causes the inline to not actually perform a search. It basically serves as a template into which we are feeding values. The action requires a little special casing since [Lasso_CurrentAction] returns a string rather than a keyword.

Second, the tags which are used to implement custom data sources [Action_AddInfo], [Action_AddRecord], and [Action_SetFoundCount] are used to recreate the found set within the inline.

Finally, [Run_Children] is used to execute the contents of the container tag within the contents of the artificial inline.

<?LassoScript

Define_Tag: 'inline_recall', -Required='inline', -Container;

  local: 'action' = #inline->(Find: 'lasso_currentaction');

  If: #action >> 'search';

    #action = (Array: -search='');

  Else: #action >> 'findall';

    #action = (Array: -findall='');

  Else;

    #action = (Array: -nothing='');

  /if;

  Inline: -StatementOnly,

      #action,

      -Database=#inline->(Find: 'database_name'),

      -Table=#inline->(Find: 'table_name'),

      -KeyField=#inline->(Find: 'keyfield_name'),

      -Schema=#inline->(Find: 'schema_name'),

      -MaxRecords=#inline->(Find: 'maxrecords_value'),

      -SkipRecords=#inline->(Find: 'skiprecords_value');

    Local: 'info' = (Array);

    Iterate: #inline->(Find: 'field_names'), (Local: 'field');

      #info->(insert: (Array: #field, False, 'text', 'none'));

    /Iterate;

    Action_AddInfo: #info;

    Iterate: #inline->(Find: 'records_array'), (Local: 'record');

      Action_AddRecord: #record;

    /Iterate;

    Action_SetFoundCount: #inline->(Find: 'found_count');

    Return: Run_Children;

  /Inline;

/Define_Tag;

?>

 

The following code runs each of the examples from this tip. This code and the two custom tag definitions above should be pasted in the same file to see how all of the examples work.

!Insert Lassoscript here

// Note - [Ex_Match_Element] from

// <<a href="http://www.omnipilot.com/TotW.1768.9101.lasso">http:/<wbr />/<wbr />www.omnipilot.com/<wbr />TotW.1768.9101.lasso</a>>.

Define_Type: 'Ex_Match_Element';

  Local: 'position';

  Local: 'value';

  Define_Tag: 'oncreate', -Required='position', -Required='value';

    Self->'position' = #position;

    Self->'value' = #value;

  /Define_Tag;

  Define_Tag: 'oncompare', -Required='value';

    Return: Self->'value' == #value->(Get: Self->'position') ? 0 | -1;

  /Define_Tag;

/Define_Type;

// Note - [Ex_Compare_Element] from

// <<a href="http://www.omnipilot.com/TotW.1768.9109.lasso">http:/<wbr />/<wbr />www.omnipilot.com/<wbr />TotW.1768.9109.lasso</a>>.

Define_Type: 'Ex_Compare_Element';

  Local: 'position';

  Local: 'type';

  Define_Tag: 'oncreate', -Required='position', -optional='type';

    Self->'position' = #position;

    Self->'type' = (if_null: (Local: 'type'), 'string');

  /Define_Tag;

  Define_Tag: 'invoke', -Required='left', -Required='right';

    Protect;

      Local: 'left_element' = #left->(Get: Self->'position');

      Local: 'right_element' = #right->(Get: Self->'position');

      If: Self->'type' >> 'dec';

        Return: (Decimal: #left_element) > (Decimal: #right_element);

      Else: Self->'type' >> 'int';

        Return: (Integer: #left_element) > (Integer: #right_element);

      Else;

        Return: #left_element > #right_element;

      /if;

    /Protect;

    Return: false;

  /Define_Tag;

/Define_Type;

// Note - The $myInline structure is generated synthetically for this example.

// [Inline: -Database='Contacts', -Table='People', -FindAll]

// [Var: 'myInline' = Inline_Store]

// [/Inline]

//

Var: 'myInline' = (Map:

  'records_array' = (Array:

    (Array: 'John', 'Doe', '21'),

    (Array: 'Jane', 'Doe', '22'),

    (Array: 'Bob', 'Surname', '45'),

    (Array: 'Tia', 'Lastname', '35')),

  'field_names' = (Array: 'First', 'Last', 'Age'),

  'found_count' = 4,

  'skiprecords_value' = 0,

  'maxrecords_value' = 50,

  'database_name' = 'Contacts',

  'table_name' = 'People',

  'keyfield_name' = '',

  'schema_name' = '',

  'lasso_currentaction' = 'FindAll');

'<hr />Recall Inline<br />';

Inline_Recall: $myInline;

  Records;

    Field: 'First'; ' '; Field: 'Last'; ' '; Field: 'Age'; '<br />';

  /Records;

/Inline_Recall;

Var: 'AgeField' = $myInline->(Find: 'field_names')->(FindPosition: 'Age')->First;

Var: 'mySubQuery' =  $myInline->(Find: 'records_array')->(Find:

(Ex_Match_Element: $AgeField, (Match_Range: 30,50)));

$myInline->(insert: 'records_array' = $mySubQuery);

$myInline->(insert: 'found_count' = $mySubQuery->size);

'<hr />Filter Age From 30 to 50<br />';

Inline_Recall: $myInline;

  Records;

    Field: 'First'; ' '; Field: 'Last'; ' '; Field: 'Age'; '<br />';

  /Records;

/Inline_Recall;

Var: 'LastField' = $myInline->(Find: 'field_names')->(FindPosition: 'Last')->First;

$myInline->(Find: 'records_array')->(SortWith: (Ex_Compare_Element: $LastField));

'<hr />Sort By Last Name<br />';

Inline_Recall: $myInline;

  Records;

    Field: 'First'; ' '; Field: 'Last'; ' '; Field: 'Age'; '<br />';

  /Records;

/Inline_Recall;

?>

Author: Nohl Lyons
Created: 11 Aug 2008
Last Modified: 2 Mar 2011