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

Community Show & Tell
1

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.

4 Likes