InfoQube IM - Community

Submitted by gregory on 2015/01/20 06:01

Some strange behaviour associated with SQL use from InfoQube

I observe strange behaviour when I try to make use of SQL from InfoQube.

I have created two fields, which I have tried to make as identical as possible. The fields are called FieldA and FieldB. I have then defined two grids, called GridA and GridB based on fields FieldA and FieldB respectively. I have defined further fields called ValidA and ValidB which are based on GridA and GridB respectively.

ValidA is a text field. In the editor section of the properties for the field, I specify:

SELECT item AS ValidA FROM GridA and also tick “Entry must be in list”.

ValidB is a text field. In the editor section of the properties for the field, I specify:

SELECT item AS ValidB FROM GridB and also tick “Entry must be in list”.

Figure 1 shows the field properties for FieldA:

The contents of GridA are in Figure 2.

The contents of GridB are in Figure 3.

When I seek to use the drop-down field ValidA, the values available to me are shown in figure 4. Note that several valid values are omitted from this list.

I choose the value A4, and the value is stored OK.

When I seek to use ValidB, values available to me are shown in figure 5. This list appears to be just the TLI values in FieldB.

In neither case are all the available values displayed. In the case of ValidA, there are some missing values. In the case of ValidB, and in several other cases which I have tried, only the TLI values are returned.

Despite the setting “entry must be in list”, it is possible to edit the value chosen and set it to anything you like - figure 6 Point 1 shows the invalid value B3Invalid. This value should have been rejected because it does not form a part of the record set returned by the SQL SELECT statement.

Closing and then reopening the application makes no difference to this behaviour.

I have then amended the SQL statement to add the clause ORDER BY item DESC for both ValidA and ValidB.

The results for ValidA are frankly bizarre, in that the order has changed but is incorrect - see figure 7.

The results for ValidB make are also incorrect - see figure 8.

Notice how the order has changed, but it is giving incorrect answers.

I have then added four items to GridB, all of which exist in FieldA. See figure 9 point 1.

I have amended the SQL for ValidB so that it now reads:

SELECT GridB.item AS ValidB FROM GridA, GridB WHERE GridB.FieldB = GridA.FieldA ORDER BY GridB.item DESC

This SQL statement does not work. When I now click on the drop-down list in ValidB the message displayed is:

Cannot show the list.

The query:

SELECT GridB.item AS ValidB FROM GridA, GridB WHERE GridB.FieldB = GridA.FieldA ORDER BY GridB.item DESC

could not be executed.

If the SQL references an IQ grid, this grid must be set to be visible to other applications (grid properties > options)

When I consult the documentation, most of the examples use the syntax of the underlying queries using macron (overline) symbols– which is nowhere explained.

Again, I am happy to amend the standard documentation if this behaviour can be explained and/or if better syntax is available – but I need the help of Pierre to be able to do this.

Comments

Hi Mark,

I haven't got the time to read the whole thing completely, but your first screenshots seem to tell me that some sub items are meeting the source and others aren't (but we can't be sure since we don't get to see the GridA or GridB fields data for each item). So, when you use your "SELECT item AS ValidA FROM GridA" or "SELECT item AS ValidB FROM GridB" queries, you get to see items from GridA a GridB that meet the source (which itself is the query that populates the grids.) Sub items, even if visible probably don't all meet the source. Some do, and some don't.

Tell us if that was the problem.<

[Edit : if you want all items -- subs and TLIs -- to appear in your drop downs, all will need to meet the source. Otherwise, only the TLIs will appear there -- unless some subs meet the source, as it seems to be in your example]

[Edit2 : tried to reproduce the problem and I think my diagnosis was right. As for the sorting, not sure why but it doesn't seem to work right, especially when subs enter the picture : maybe because the grid's sort and the query sort conflicts in some way?]

-------------------------------------------------------

Windows 8.1

Sony Vaio S Series 13 (SVS131E21L)

Ram:8gb, CPU: Intel i5-3230M, 2.6ghz

Hi Menez and thanks - your suggestions make sense, although it will take me a while to work through the consequences. Certainly the key lies in defining the grids properly. I will report back when I have done a little bit more work.

Regarding the sorting problem: I suddenly recalled an ancient discussion on this forum which can be found at: www.sqlnotes.net/drupal5/index.php. Briefly, fields in InfoQube are based on what used to be called a memo field in Microsoft Access (now renamed long text). InfoQube uses the so-called JET database engine which is also used in (desktop) Microsoft Access. This database engine implements only a subset of the full SQL standard and imposes a number of restrictions. One among these is that you cannot sort on a memo field - and in InfoQube, all text items are implemented as memo fields in the underlying database

There is a workaround, which is to use the syntax ORDER BY left(fieldname, 255) ASC. I can confirm that this works in InfoQube; redefining the field ValidA as

SELECT Item AS ValidA FROM FieldA ORDER BY left(Item, 255) ASC

gives drop-down values:

More to follow later...

Mark GREGORY, Redon, France - GMT +1/+2; EST +6

[quote=gregory]