Using either additionalScope -or- value from component in one query

There are two ways to get properties into your query.

The more common way is to use what I'll call app variables. These can be values from a component, another query, a transformer or temp variable. An example would be this where we use a value from the selected row in a table:

-- qryAddSection
INSERT INTO template_sections (template_id, town_id, product_types, bundle_types)
VALUES ( ({{tblTemplates.selectedRow.data.template_id}}, 307, 17, 6 )

But you can also pass this template_id using additonalScope when triggering the query from a javascript query like this one which adds a new template and then adds a new section.

// jsNewTemplate
  (async () => {
    // Insert new parent record
    let data = await qryTemplateInsert.trigger()
    // data.result now holds the new record(s) which was added.
    // Insert new child record
    await qryAddSections.trigger({additionalScope: {template_id: data.result[0].template_id}})
    // Requery to refill the parent (and cascade to child tables)
    await qryTemplatesSelect.trigger()
})();

But you would need a different query which would look like this:

-- qryAddSection2
INSERT INTO template_sections (template_id, town_id, product_types, bundle_types)
VALUES ( ({{template_id}}, 307, 17, 6 )

Two queries to maintain? Ouch!

Well let's do this:

INSERT INTO template_sections (template_id, town_id, product_types, bundle_types)
VALUES (
({{typeof(template_id)=='undefined' ? tblTemplates.selectedRow.data.template_id : template_id }}, 307, 17, 6)

Now you can use just one query to add your record from either workflow.

3 Likes