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;
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> |