Foreign key conundrum

Technical Q&A
1

For some reason, my foreign key fields in data sources are not coming through to Visual Builder.

I am definitely missing something. I do not see a Data Binding set of properties, nor an optionDataSource property for any form item from dropping the Community datasource. This is also true of dynamicform fields I drop to get selectItems or comboboxes to hook up.

Below are the form fields with foreign key declarations. They use the following Foreign Key entries in the Community datasource.

License.LICENSEID
PrintTemplate.PRINTTEMPLATEID
PrintTemplate.PRINTTEMPLATEID
PrintTemplate.PRINTTEMPLATEID

<DynamicForm numCols="10" dataSource="ref:Community" ID="CommunityDetailForm" autoDraw="false">
    <fields>
    .
    .
    .
        <FormItem type="text" name="LICENSEID" title="License" constructor="TextItem"/>
        <FormItem name="DEFAULTAGENDATEMPLATEID" title="Agenda Template" constructor="TextItem"/>
        <FormItem name="DEFAULTMINUTESTEMPLATEID" title="Minutes Template" constructor="TextItem"/>
        <FormItem name="DEFAULTREPORTTEMPLATEID" title="Report Template" constructor="TextItem"/>
    .
    .
    .
    </fields>
    <titleOrientation>left</titleOrientation>
    <wrapItemTitles>true</wrapItemTitles>
    <validateOnChange>true</validateOnChange>
    <autoFetchData>true</autoFetchData>
</DynamicForm>
2

Well, I have one field with a foreign key working. I deleted the native field for the FK and dropped a selectitem in its place with the same name. Then I set the Value and Display fields under Display Values to the foreign table key column and display column names. Note I set the new selectitem Name to be the foreign key column name.

This worked when the foreign key column name was the same as the foreign table key column name. But, it fails when this is not true. I know that the foreign key column name is correct because it shows the right ID values.

I must have a mistake somewhere. I am encouraged to keep trying.

Experimenting, I find that I can delete the Value display field. It defaults to the formitem name. Oddly enough, the one field that works does not have an optionDataSource. I have a grid field for the same column and it works just fine as well. It, however, when I check the Code, has an explicit optionDataSource.

So, there must be some implicit optionDataSource that is being supplied. In my case, it works when I just have one FK field to another table.

It does not work when I have more than one FK to the same table. These must have different names because they each select different records from that same table. The [implicit] optionDataSource cannot be located because the FK column name is not the same as any other name involved.

This would not be a problem if I could get explicit access to a dynamicform field optionDataSource like I can for a listgrid field.

I would programmatically set this optionDataSource for the form fields but I have yet to find an event. I cannot even get the dataViewLoaded event to fire.

As ever I press on.

3

A new wrinkle. I revised my database to add a new table to match the foreign key rules. I also updated the datasource with the foreign key. I know I got things right because I can see the populated dropdown values in VB.

However, when I SAVE the app and run it in a new window, the values do not appear in that same selectitem field. I cannot see anything in the developer console log about these ID fields, except loading of the datasources and the SELECT statements to fetch their values. So, I am not quite clear on what is different.

I think I have to acquiesce this thread at this time. This is probably as close as I can get with this particular design choice.

So, I am abandoning it. Interestingly, I tried to simplify something by combining 3 tables into one. Now, I need to undo that and go back to the original 3 tables. I was not going to be able to set static criteria on a dropdown list, anyway.

By the way, I experimented with Fetch Related Data and it worked first time out of the box. This has to be the best thing since sliced bread. And, I really like sliced bread. Good work, ladies and gentlemen. Now, I need to figure out how to remember some ‘current’ values. I need to set these values and use them to retrieve other related records. My users will [almost] always be using a subset of available records because they will belong to only one Community, for example.

In the morning, after coffee, I will see if I need just a client-side datasource, or a Javascript global object, or something else that I can plug into a String Method.

Good night.

4

OK. This one is not going to be a never mind. I have been all over this without finding an answer. The 3 bad selectitems show the dropdown control, but only the key value currently in the ID field. And, when clicked it only shows one row. I know from experience that if it saw the 2 records in the database it would show 2 blank rows, even if it had the Display Field wrong.

The latest: since another selectitem was working, I made just a few changes to a problematic one to match it. I moved the formitem name and Display Field. VB picked up the foreign key and it worked. So, I know at least that I have not disabled or corrupted the selectitem.

Actually, I have two of them working. One points to Account, the other License. When I look at the server log I can see the fetches for both of these. I see no attempted fetches for the three problematic datasources. I saw no difference between the XML code among the five.

<DynamicForm numCols="8" dataSource="ref:Community" ID="CommunityDetailForm" autoDraw="false">
    <fields>
        <FormItem name="COMMUNITYID" title="Communityid" constructor="TextItem">
            <visible>false</visible>
        </FormItem>
        <FormItem name="COMMUNITYNAME" title="Community Name" constructor="TextItem"/>
        <FormItem name="ACCOUNTID" title="Account" constructor="SelectItem">
            <displayField>ACCOUNTNUMBER</displayField>
        </FormItem>
        <FormItem name="LICENSEID" constructor="SelectItem">
            <displayField>LICENSENAME</displayField>
        </FormItem>
        <FormItem name="ENABLED" title="Enabled" constructor="CheckboxItem"/>
        <FormItem name="CONTACTNAME" title="Contact" constructor="TextItem"/>
        <FormItem name="CONTACTEMAIL" title="Email" constructor="TextItem">
            <colSpan>1</colSpan>
        </FormItem>
        <FormItem name="EXPIREDATE" title="Expire" constructor="DateItem"/>
        <FormItem name="CONTACTPHONE" title="Phone" constructor="TextItem">
            <startRow>true</startRow>
        </FormItem>
        <FormItem name="LOCATION" title="Location" constructor="TextItem"/>
        <FormItem name="TIMEZONE" title="Time Zone" constructor="TextItem"/>
        <FormItem name="LANGUAGE" title="Language" constructor="TextItem">
            <startRow>true</startRow>
        </FormItem>
        <FormItem name="NUMBERFORMAT" title="Number Format" constructor="TextItem"/>
        <FormItem name="DATEFORMAT" title="Date Format" constructor="TextItem"/>
        <FormItem name="AGENDATEMPLATEID" constructor="SelectItem">
            <startRow>true</startRow>
            <displayField>TEMPLATENAME</displayField>
        </FormItem>
        <FormItem name="MINUTESTEMPLATEID" constructor="SelectItem">
            <displayField>TEMPLATENAME</displayField>
        </FormItem>
        <FormItem name="REPORTTEMPLATEID" constructor="SelectItem">
            <displayField>TEMPLATENAME</displayField>
        </FormItem>
        <FormItem name="AGENDAPRINTOPTIONS" title="Agenda Print Options" constructor="TextItem">
            <startRow>true</startRow>
        </FormItem>
        <FormItem name="MINUTESPRINTOPTIONS" title="Minutes Print Options" constructor="TextItem"/>
        <FormItem name="REPORTPRINTOPTIONS" title="Report Print Options" constructor="TextItem"/>
        <FormItem name="DESC" title="Description" constructor="TextAreaItem">
            <titleOrientation>top</titleOrientation>
            <width>*</width>
            <height>*</height>
            <colSpan>5</colSpan>
            <rowSpan>5</rowSpan>
            <startRow>true</startRow>
        </FormItem>
        <FormItem name="NOTES" title="Notes" constructor="TextAreaItem">
            <titleOrientation>top</titleOrientation>
            <width>*</width>
            <height>*</height>
            <colSpan>3</colSpan>
            <rowSpan>5</rowSpan>
        </FormItem>
        <FormItem name="LOG" title="Log" constructor="TextAreaItem">
            <titleOrientation>top</titleOrientation>
            <width>*</width>
            <height>*</height>
            <colSpan>8</colSpan>
            <rowSpan>6</rowSpan>
            <startRow>true</startRow>
        </FormItem>
        <FormItem name="CREATED" title="Created" constructor="TextItem">
            <startRow>true</startRow>
        </FormItem>
        <FormItem name="MODIFIED" title="Modified" constructor="TextItem"/>

I even changed the DDL so that the problem FK’s were NOT NULL.

CREATE TABLE Community(
		CommunityID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 101, INCREMENT BY 1),
		AccountID BIGINT NOT NULL,
		CommunityName VARCHAR(64) NOT NULL,
		LicenseID BIGINT NOT NULL,
		Enabled BOOLEAN DEFAULT TRUE,
		ExpireDate DATE,
		ContactName VARCHAR(64),
		ContactEmail VARCHAR(64),
		ContactPhone VARCHAR(32),
		Location VARCHAR(64),
		TimeZone VARCHAR(64),
		Language VARCHAR(64),
		NumberFormat VARCHAR(64),
		DateFormat VARCHAR(64),
		AgendaTemplateID BIGINT NOT NULL,
		AgendaPrintOptions CHAR(64),
		MinutesTemplateID BIGINT NOT NULL,
		MinutesPrintOptions CHAR(64),
		ReportTemplateID BIGINT NOT NULL,
		ReportPrintOptions CHAR(64),
		Desc VARCHAR,
		Log VARCHAR,
		Created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT IDX_CommunityName UNIQUE (CommunityName),
	CONSTRAINT IDX_Community_License FOREIGN KEY (LicenseID) REFERENCES License (LicenseID),
	CONSTRAINT IDX_Community_Account FOREIGN KEY (AccountID) REFERENCES Account (AccountID),
	CONSTRAINT IDX_Community_AgendaTemplate FOREIGN KEY (AgendaTemplateID) REFERENCES AgendaTemplate (AgendaTemplateID),
	CONSTRAINT IDX_Community_MinutesTemplate FOREIGN KEY (MinutesTemplateID) REFERENCES MinutesTemplate (MinutesTemplateID),
	CONSTRAINT IDX_Community_ReportTemplate FOREIGN KEY (ReportTemplateID) REFERENCES ReportTemplate (ReportTemplateID)
);

Well, I have to move on and this is not stopping that. But, I think I need some help in diagnosing what problem I have created.

I sort of finished the first app. It starts OK, but I really need to set it up so that it starts following a login that verifies the login, chooses which app starts, and passes context parameters to start the session.

And so onward I go.

5

My FK problems are piling up. Attached are two screen shots.

The VB app result shows 5 tasks with their Project# and Issue#. (Tasks are children of Issues and grandchildren of Projects in my system.)

Problem #1 is that the Issue Title does not fetch from the Issue table. AutofetchDisplayMap is on for this field. And, it has the right fields as far as I know.

<ListGrid dataSource="ref:Task" ID="ListGrid8" autoDraw="false">
    <fields>
        <ListGridField name="PROJECTID" title="Project#">
            <optionDataSource>Project</optionDataSource>
            <displayField>PROJECTNUMBER</displayField>
            <autoFetchDisplayMap>true</autoFetchDisplayMap>
        </ListGridField>
        <ListGridField name="ISSUEID" title="Issue#" align="right">
            <optionDataSource>Issue</optionDataSource>
            <displayField>ISSUENUMBER</displayField>
            <autoFetchDisplayMap>true</autoFetchDisplayMap>
        </ListGridField>
        <ListGridField name="ISSUETITLE" title="Issue Title">
            <optionDataSource>Issue</optionDataSource>
            <valueField>ISSUEID</valueField>
            <displayField>ISSUETITLE</displayField>
            <autoFetchDisplayMap>true</autoFetchDisplayMap>
        </ListGridField>
        <ListGridField name="TASKNUMBER" title="Task#"/>
        <ListGridField name="TASKTITLE" title="Task Title"/>
        <ListGridField name="STATUS" title="Status"/>
        <ListGridField name="DUEDATE" title="Due"/>
    </fields>
    <autoFetchData>true</autoFetchData>
</ListGrid>

Problem#3 is that Project# and Issue#, which show in VB do not show when I run the app. I know there are differences. But, I think I have everything in the right places for this to work.

Well, forget the attachments. VB hung up before I could capture it and the server log froze. All I saw was a message about server communication.

Now, none of the FK’s fetch anything.

Right now I am using SmartClient_SC_SNAPSHOT-2012-01-09_v82p. My thought was that I should just grab a stable release and keep with it. Am I creating trouble for myself by not keeping up with the nightlies?