InfoQube IM - Community

Submitted by mknjlaw on 2010/01/23 21:09

In a relational database of two tables: legal concepts & cases, there is a many-to-many relationship. One legal concept (right against self incrimination, for example) may be related to many cases, and one case maybe related to many legal concepts.

In InfoQube I have created two "grids" (tables), one for Legal Concepts (with no fields) and one for Cases with several fields (case name, citation, date, jurisdiction, court, and judge). What makes InfoQube wonderful is how easily records within a database can be easily manipulated in an outline metaphor. But somehow I need to be able to relate multiple cases to one Legal Concept and its progeny (siblings and siblings of siblings, etc)

Any ideas would be appreciated

Mitchell Kastner

732 873-9555

Comments

Hi Mitchell,

Although IQ is not a true relational database, it can mimic some relational DB behavior.

In your case (no pun intended), it's very easy to relate any case to any legal concept, as many times as you want. But for this you'll need to have cases as children of your legal concepts.

For this you just need to drag and drop items (cases) under different parents (legal concepts) while pressing the ctrl key -- what that does is to give an item (a "case") another parent (another legal concept).

An item can have as many parents as you want. The item will look as if it was duplicated, while in fact it's still the same item but with different parents.

There are other ways to build such relations, using hyperlinks, or wikitags, etc. But to me this seems like the easiest.

Armando, Are you saying that I should have two tables (grids), enter a record in the Cases table and then drag and drop it as a child record in the Legal Concepts table? If so, on the Legal Concepts table, how could I filter the records to show only those records with a particular legal concept? Much thanks Mitchell Kastner

>Are you saying that I should have two tables (grids), enter a record in the Cases table and then drag and drop it as a child record in the Legal Concepts table?

Yes this is pretty much it. Did you also see my other post with the screenshot ? This could give you ideas of how to organize your cases and legal concepts, with colour coding etc. I can help you with details if you want.

>If so, on the Legal Concepts table, how could I filter the records to show only those records with a particular legal concept?

I guess that by table you mean "Grid" ?

Simple... you'd set a filter (in the filter text box in the source bar -- to see the source bar : press alt-s or select it in the Grid menu) like (depending on where is your legal concept defined in the hierarchy):

item like "legal concept 1"

(you can use wildcards like *. e.g. : item like "legal conc*"

Here's how your source bar "would" look (of course the field names in the source and filter textbox will vary depending on your own choices of field names) :

Of course you could just go to the legal concept you want to explore... and expand the hierarchy, and maybe "hoist" it (right click on item : hoist selected items -- this will isolate the item and its sub items in the grid).

Or you could use the column filter. First expand the whole outline (ctrl-9 will generally be enough : expands to the ninth level). Right click on the appropriate column header, and select "filter column". Write the String you're after. You can use wildcards like *.

e.g. : *legal conce*

Will show all items that have the "legal conce" string, somewhere in that column, regardless of the strings position, etc.

Does that answer your question ?

There are probably other ways of showing your cases -- like using a field in which the legal concept name would be automatically, and then propagated to the entire hierarchy. But this particular solution seems less convenient and flexible then the one I explained before.

There are also other ways of organizing your data. But it all depends on what you want to do with it. The more you know how you might want to use it in the future, the easier it'll be to know how how to organize it.

Just a few more words...

1- Are you familiar with IQ's interface and functionning ? (the grid, the source bar, the way fields are automatically assigned to items when they are created in grids, etc.)

If not, may suggest that you have a look at the first manual pages, especially the -- link to nonexistent node ID 860 -- and Items, Values and Views from the getting started section. It's not always easy to follow, but it'll might give you a few ideas about possibilities on how to structure your data. This is not to replace support in the forum of course.

Note that when an item is created in a grid 9top level), a field which corresponds to grid source is automatically assigned to it. That isn't true of the sub items and this behavior has to be taken into consideration when filtering. I won't go into details now as it all depends on how you want to use your data. But if you need more info because you're running into difficulties, please ask.

2- Ctrl-drag&drop is not the same as plane & simple drag&drop. Ctrl-drag&drop allows items to have several parents links. Simple drag&drop just moves the item, creating a new parent link if necessary, and sometimes removing some field data (can explain if necessary).

Armando, First, my heartfelt thanks to Pierre and you. Just fabulous. Second, can I use multiple operators (AND OR NOT)if there is only one field/column on which to perform the query/search/filter? With Pierre's grid design there would be multiple columns/fields in the Case grid each of which can be popluated from an item from the Legal Concepts grid. Under that design, I assume, I could perform a multiple operator search/filter/query using different boolean operators. Trust me it would be amazingly helpful to use your grid design, and frankly I just the way it looks better than how I visualize Pierre's design (too many columns :>))But I get drill down more precisely if I can use multiple field values in the query. Again, Pierre and your help has been awesome. (Pierre, although I am old---60---I pride myself on mastering any app I have tried. And I willing to give InfoQube all the time necessary to master it. Do you think however that I might be best off starting with a beginner's guide to SQL?)

[quote=Mitchell Kastner]Armando, First, my heartfelt thanks to Pierre and you. Just fabulous. Second, can I use multiple operators (AND OR NOT)if there is only one field/column on which to perform the query/search/filter? With Pierre's grid design there would be multiple columns/fields in the Case grid each of which can be popluated from an item from the Legal Concepts grid. Under that design, I assume, I could perform a multiple operator search/filter/query using different boolean operators. [/quote]

You are most welcome.

The short answer is : yes you can use very complex queries, with boolean operators (and, or, not...), < > = signs, numbers, text, dates, etc.

The more complex answer lies in the section on filtering and searching : 2. Filtering, and its subsections, like Grid Source EditBox and Source Filter Editbox

It tries to explain the basics of filtering. It's not hard but there are several ways of doing it. Some might suit you better than others. Experiment, and don't be shy to ask questions ! Filters are not that difficult to manipulate, but since IQ can be configured in different ways they don't always necessarily proiduce the expected results, at first.

[quote=Mitchell Kastner]Trust me it would be amazingly helpful to use your grid design, and frankly I just the way it looks better than how I visualize Pierre's design (too many columns :>))But I get drill down more precisely if I can use multiple field values in the query. Again, Pierre and your help has been awesome. (Pierre, although I am old---60---I pride myself on mastering any app I have tried. And I willing to give InfoQube all the time necessary to master it. [/quote]

Use what's best for you ! You could even try both design on a very small scale, as a test, and see how it goes. IQ is so flexible.

I personally use both designs for different purposes. I use multiple columns/fields when what I need is best served by a table format where I plan to have a lot of items crossing a limited amout of fields and where it'd be easier to enter the data this way : just press tab to navigate and quickly fill/check the various field/columns. However, the other solution can be good when you want to see things displayed herarchically, or grouped in different ways or when you want to be able to add details as subitems of any item -- it's harder to do that with fields (you can't really take notes inside a Yes/No field... even if sometimes you wished you could... A limited amount of text material can be entered in the field description though in the Field management dialog -- view -> manage fields ) even if there are work arounds, etc.

Both methods could also be combined... to a point. E.g. : you could have cases which would appear as items, but which would also be identified/characterized by various fields. And both methods can be used to produce pivot tables and charts... But strategies would be slightly different. It all depends on what you need and want.

Have fun !

[EDITED]

[quote=Mitchell Kastner]Do you think however that I might be best off starting with a beginner's guide to SQL?)[/quote]

Forgot about this... sorry.

hmmm.... I'd say that you probably don't really need that at first. I don't really know SQL myself ! And 99% of the time I manage to get everything I need from my database with simple filters.

But there are lots of resource on the web if you need a bit more SQL proficiency.

Thanks Armando for this excellent reply.

I'll simply add one thing, which is not IQ specific, but taken from 20+ years of consulting... :

You must ask yourself what kind of analysis / compilation / summary you want from your data.

This (and the tools you want to use to do this analysis) has a strong impact on how you should format / arrange your raw data

If you need little or no analysis (other than some human driven visual one), you have many ways to organize and you can try a few and see which one is (1) the easiest to configure, (2) the fastest to enter data, (3) the most visually appealing.

If you need more profound analysis (export to Excel for additional calculations, import from / export to other databases, pivot tables and charts), then you must make sure that your organization scheme will allow it.

Hi Mitchell,

What Armando suggested is one good way to do it. I'll suggest another and you can choose which suits you better. I'm assuming that a case is related to a small number of legal concepts (<=5)

Keep you legal concepts grid as it is
Create a few text fields, name them CaseConcept1, 2, 3, ...
For each field, in the Editor section >> Pop-up list, enter:
select Item as CaseConcept1 from [Legal Concepts]

(I'm assuming you grid is named "Legal Concepts", replace 1 by 2, 3, etc for each field
In your Case grid, add these new fields (as columns)
Now for each case, you can assign n concepts, the list comes from the Legal Concepts grid

The good side of this:

Simple

The bad side of this:

Doesn't play too well with pivot table (cannot answer what if questions of type "How many cases of type X in last year"

Another approach, one which works well with pivot tables is to create a field for each legal concepts and to checkmark each one which applies for a particular case. With the new (to be released) field hierarchy, this becomes a more interesting approach

HTH

Pierre

Pierre, I don't understand your instructions. In MSACCESS, it is simple enough to create a field in a table using a drop down pick list field which is popluated with field values from a related table. Are you saying that in my Case table I should create three or four fields, Legal Concept 1, 2, & 3, using field values from the Legal Concepts table? That is an interesting idea because your boolean search capabilities could be complex: Select all cases with Self-Incrimination AND Non-Custodial NOT Garrity Rule. That would be too cool. Assuming I am interpreting your instructions correctly. BTW: how do you create queries in Infoqube?

[quote=Mitchell ]

Are you saying that in my Case table I should create three or four fields, Legal Concept 1, 2, & 3, using field values from the Legal Concepts table?
That is an interesting idea because your boolean search capabilities could be complex: Select all cases with Self-Incrimination AND Non-Custodial NOT Garrity Rule. That would be too cool. Assuming I am interpreting your instructions correctly.
BTW: how do you create queries in Infoqube?[/quote]

Yes and no. You don't have a Case table, but a Case Grid (which in reality is not a table, but a query). So fields don't belong to tables (as they do in Access). So my first proposition is to create a few fields, have the pop-up editor take its list come from the Legal Concept grid. If the number of concepts is not too large (<20), my second proposal is more flexible, especially with the coming field enhancements
Yes. But my second proposal would work even better
A grid is a query. A field is also a query. See Smart Fields (aka smart folders)

Or see the manual, which is the same topic but with the forum discussion removed: 4.91 Smart Fields (aka smart folders)

Mark Gregory, Rennes, France - GMT +1/+2; EST +6

I was going to post this 1.5 h ago, but the server seemed to be slow... So here it is, after Pierre's own suggestion.

Here's an example. (Did that in 2 min.)

1- I first created a general "Legal concept" Item (which I'll be using as a broad category for all legal concepts)

2- I entered 3 legal concepts ("legal concept 1", ...2, ...3)

3- Under each "Legal concept x" I created an item called "Cases" under which I'll put various cases ; I also created an item called "Related legal concepts" under which I'll put other related legal concepts.

4- I then created 4 cases ("Case A",... B,... C,... D)

5- and ctrl-dragged and dropped these cases under some of the "legal concept x" items', and actually, under the subitem "Cases".

6- I also ctrl-dragged and dropped "legal concept 1" and "legal concept 2" under the "Related legal concepts" subitem of the legal concept 1 item, just for the fun of it.

In the screenshot, I left the Iditem column so that you can compare the items wih multiple parents and see that they are truly the same items with the same ID... only, they have different parents.

The good thing with this method is that you cans easily see (through the properties pane for instance, or other means, how many parents (..."legal concepts") a SUBitem ("cases") has, and you can even see their names... All that in the properties pane, of course.

Hopefully explanations are clear enough. It's harder to explain then to do, of course... ;)

[EDIT : note that you can also color code legal concepts, cases, etc. This can be done automatically trough conditional formatting (see Item Formatting) or even vb scripting, if you assign specific "case" or "Legal concept" fields that you can check to differentiate the different items. That's one way of doing it, there are others...]