Confiforms join Forms how to?

jeff lansing December 12, 2019

I am trying to recreate the following SQL example using Confiforms  Forms (as tables) and Views (as SQL Queries):

DROP TABLE COMPONENT;

CREATE TABLE COMPONENT(SWID INT PRIMARY KEY, NAME VARCHAR(255), VERSION VARCHAR(10));

INSERT INTO COMPONENT VALUES(1, 'Office', '2016');

INSERT INTO COMPONENT VALUES(2, 'Visio', '2016');

DROP TABLE BASELINE;

CREATE TABLE BASELINE(BID INT PRIMARY KEY, VERSION VARCHAR(10), NWCLASS VARCHAR(20));

INSERT INTO BASELINE VALUES(1, '1.0.0.0', 'Corporate');

INSERT INTO BASELINE VALUES(2, '1.0.0.0', 'Engineering');

DROP TABLE B2C;

CREATE TABLE B2C(BID INT, SWID INT);

INSERT INTO B2C VALUES(1, 1);

INSERT INTO B2C VALUES(2, 1);

INSERT INTO B2C VALUES(2, 2);


SELECT * FROM COMPONENT;

SWID  

NAME  

VERSION  

1

Office

2016

2

Visio

2016


 SELECT * FROM BASELINE;

BID  

VERSION  

NWCLASS  

1

1.0.0.0

Corporate

2

1.0.0.0

Engineering


SELECT B.VERSION, B.NWCLASS, C.NAME, C.VERSION
FROM BASELINE AS B
JOIN B2C AS J
ON B.BID = J.BID
JOIN COMPONENT AS C
ON J.SWID = C.SWID;

VERSION  

NWCLASS  

NAME  

VERSION  

1.0.0.0

Corporate

Office

2016

1.0.0.0

Engineering

Office

2016

1.0.0.0

Engineering

Visio

2016

So there are 2 information tables called BASELINE and COMPONENT, and a join table called B2C, and the query that I want to imitate in Confiforms uses the join table to produce rows that describe a particular deployment.

I have tried using TableView Merger (as shown below) but without much understanding and without much success, For all I know that may be a totally misguided way to approach the problem anyway, but I couldn't find any examples on the web that joined Forms, so I tried that.

<p class="auto-cursor-target">
  <br/>
</p>
<ac:structured-macro ac:macro-id="5bfdbd0a-c03b-4091-8a98-c9b13f3a50e0" ac:name="confiform" ac:schema-version="1">
  <ac:parameter ac:name="formName">component</ac:parameter>
  <ac:rich-text-body>
    <p class="auto-cursor-target">
      <br/>
    </p>
    <ac:structured-macro ac:macro-id="34c5ade4-26cf-4ba0-8ddb-6baebfba7f98" ac:name="confiform-entry-register" ac:schema-version="1">
      <ac:parameter ac:name="registrationButtonLabel">Insert Row</ac:parameter>
      <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
      <ac:rich-text-body>
        <p>
          <br/>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <p>
      <ac:structured-macro ac:macro-id="1be25592-a765-422d-855d-ee343d684c57" ac:name="confiform-field-definition" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">swid</ac:parameter>
        <ac:parameter ac:name="fieldLabel">SWID</ac:parameter>
        <ac:parameter ac:name="type">number</ac:parameter>
      </ac:structured-macro>
    </p>
    <p>
      <ac:structured-macro ac:macro-id="14c9c2a4-d84e-47da-bcbe-1f6c7bab10c4" ac:name="confiform-field-definition" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">name</ac:parameter>
        <ac:parameter ac:name="fieldLabel">Common Name</ac:parameter>
        <ac:parameter ac:name="type">text</ac:parameter>
      </ac:structured-macro>
    </p>
    <p>
      <ac:structured-macro ac:macro-id="14c9c2a4-d84e-47da-bcbe-1f6c7bab10c5" ac:name="confiform-field-definition" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">version1</ac:parameter>
        <ac:parameter ac:name="fieldLabel">Version</ac:parameter>
        <ac:parameter ac:name="type">text</ac:parameter>
      </ac:structured-macro>
    </p>
  </ac:rich-text-body>
</ac:structured-macro>
<p class="auto-cursor-target">
  <br/>
</p>
<ac:structured-macro ac:macro-id="0e3be655-023a-4d70-bf8a-c60c52276dbe" ac:name="confiform" ac:schema-version="1">
  <ac:parameter ac:name="formName">btoc</ac:parameter>
  <ac:parameter ac:name="registrationFormTitle">b2c rows</ac:parameter>
  <ac:rich-text-body>
    <p>
      <ac:structured-macro ac:macro-id="7be7e7f2-499c-4086-9cd2-a5954f1dd0c3" ac:name="confiform-field-definition" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">bid</ac:parameter>
        <ac:parameter ac:name="fieldLabel">BID</ac:parameter>
        <ac:parameter ac:name="type">number</ac:parameter>
      </ac:structured-macro>
    </p>
    <p>
      <ac:structured-macro ac:macro-id="37f431bc-9cfc-4975-9429-6ee3de67ea40" ac:name="confiform-field-definition" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">swid</ac:parameter>
        <ac:parameter ac:name="fieldLabel">SWID</ac:parameter>
        <ac:parameter ac:name="type">number</ac:parameter>
      </ac:structured-macro>
    </p>
    <ac:structured-macro ac:macro-id="d23698df-27c3-4502-9e67-b6b74d63520d" ac:name="confiform-entry-register" ac:schema-version="1">
      <ac:parameter ac:name="registrationButtonLabel">Insert Row</ac:parameter>
      <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
      <ac:rich-text-body>
        <p>
          <br/>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <p class="auto-cursor-target">
      <br/>
    </p>
  </ac:rich-text-body>
</ac:structured-macro>
<p class="auto-cursor-target">
  <br/>
</p>
<ac:structured-macro ac:macro-id="277ec30b-9415-41c3-8797-a1b67b9d55e0" ac:name="confiform" ac:schema-version="1">
  <ac:parameter ac:name="formName">baseline</ac:parameter>
  <ac:parameter ac:name="registrationFormTitle">Baseline</ac:parameter>
  <ac:rich-text-body>
    <p>
      <ac:structured-macro ac:macro-id="113b16a3-6d9d-42bf-9d8a-774eb7e3d558" ac:name="confiform-field-definition" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">bid</ac:parameter>
        <ac:parameter ac:name="fieldLabel">BID</ac:parameter>
        <ac:parameter ac:name="type">number</ac:parameter>
      </ac:structured-macro>
    </p>
    <p>
      <ac:structured-macro ac:macro-id="930e30fe-5912-421d-bde0-c4f7e164daa1" ac:name="confiform-field-definition" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">version2</ac:parameter>
        <ac:parameter ac:name="fieldLabel">Version</ac:parameter>
        <ac:parameter ac:name="type">text</ac:parameter>
      </ac:structured-macro>
    </p>
    <p>
      <ac:structured-macro ac:macro-id="0a07d8b3-03c2-4e6e-91c3-6c47c108eaf2" ac:name="confiform-field-definition" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">nwclass</ac:parameter>
        <ac:parameter ac:name="fieldLabel">NW Class</ac:parameter>
        <ac:parameter ac:name="type">text</ac:parameter>
      </ac:structured-macro>
    </p>
    <ac:structured-macro ac:macro-id="896f817d-5155-4e0e-98bc-8a9685028818" ac:name="confiform-entry-register" ac:schema-version="1">
      <ac:parameter ac:name="registrationButtonLabel">Insert Row</ac:parameter>
      <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
      <ac:rich-text-body>
        <p>
          <br/>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <p class="auto-cursor-target">
      <br/>
    </p>
  </ac:rich-text-body>
</ac:structured-macro>
<h3 class="auto-cursor-target">Merged View</h3>
<p>
  <br/>
</p>
<ac:structured-macro ac:macro-id="a7aaa85f-cf49-459e-b67b-dddc95c719ec" ac:name="confiform-table-merger" ac:schema-version="1">
  <ac:rich-text-body>
    <p>
      <ac:structured-macro ac:macro-id="a0680e18-2b52-4b93-baa0-445487240a99" ac:name="confiform-field" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">bid</ac:parameter>
      </ac:structured-macro>
    </p>
    <p>
      <ac:structured-macro ac:macro-id="a0680e18-2b52-4b93-baa0-445487240a9a" ac:name="confiform-field" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">swid</ac:parameter>
      </ac:structured-macro>
    </p>
    <p>
      <ac:structured-macro ac:macro-id="5bc05ee3-9cae-4763-8b3b-0d23d94c31ec" ac:name="confiform-field" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">NW Class:[baseline.nwclass]</ac:parameter>
      </ac:structured-macro>
    </p>
    <p>
      <ac:structured-macro ac:macro-id="f05689d6-9ec6-4db4-9b24-a830bc61e03e" ac:name="confiform-field" ac:schema-version="1">
        <ac:parameter ac:name="fieldName">entry.component.version1</ac:parameter>
      </ac:structured-macro>
    </p>
    <ac:structured-macro ac:macro-id="c1467011-c101-4d31-a410-a2b5048b1d24" ac:name="confiform-table" ac:schema-version="1">
      <ac:parameter ac:name="formName">baseline</ac:parameter>
      <ac:rich-text-body>
        <p>
          <br/>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <p class="auto-cursor-target">
      <br/>
    </p>
    <ac:structured-macro ac:macro-id="c1467011-c101-4d31-a410-a2b5048b1d25" ac:name="confiform-table" ac:schema-version="1">
      <ac:parameter ac:name="formName">btoc</ac:parameter>
      <ac:rich-text-body>
        <p>
          <br/>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <p class="auto-cursor-target">
      <br/>
    </p>
    <ac:structured-macro ac:macro-id="b97110c0-ee54-4ca9-b69c-fb4abecc0d21" ac:name="confiform-table" ac:schema-version="1">
      <ac:parameter ac:name="formName">component</ac:parameter>
      <ac:rich-text-body>
        <p>
          <br/>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <p class="auto-cursor-target">
      <br/>
    </p>
  </ac:rich-text-body>
</ac:structured-macro>
<p class="auto-cursor-target">
  <br/>
</p>

 

0 answers

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events