Group view in datagrid

[golife]

Is there a way using the DG (data grid) to group records (rows) into record groups and subgroups and provide a group title, eventually also the sum of the totals of the associated record group inside the DG?

As a very simple example: Materials belong to the "Materials" group. Services belong to the "Services" group. Work hours with a description are items (records) of the Services group. Records for selling some hardware belong to "Materials" group. These two groups are presented as two separate record groups.

I assume that the DG would require using a separate row and row height with a group title showing the group title field and hiding all other data fields of the row. Is this correct?

I am still not too familiar with the DG. I can do the above with a lot of scripting using my own implementation of groups within groups and add titles and summaries to each group depending on the associated group of the record. But I would prefer using the DG now.

Thank you for any help...

[dunbarx]

Hi.

The DG allows you to set all kinds of properties to visually separate such subCategories as you describe, but I wonder if the result is worth the effort. I assume your goal is one single entity that contains all these features, as opposed to a handful of smaller gadgets, maybe dataGrids.

In other words, perhaps you might designate certain rows as embedded "title" rows, and perhaps identify the data beneath them, and before the next "title" row with, say, colored lines or whatever. This can be done, but you have to track the data carefully, since the number of rows in each "subGroup" will surely change.

Craig

[dunbarx]

Thinking about this some more, since the dataSets will likely change so often, maybe think about stacking a few DG's or even table fields, into a new group? From what you said it seems like a few such gadgets would act the way you want them to.

It would be far easier to manage a bunch of separate groups than to manage and display disparate dataSets within one.

I think.

Craig

[golife]

@ dunbarx, oh, thank you...)

Well, I studied the FORM version of the Data Grid a bit more. It is possible defining the entries into the Data Grid array with hierarchical numbering (for sorting) and using a label field for titles and subtitles of groups and sub groups.

All calculations of totals and subtotals of record groups must better be done using the array or using the database from where data is originally coming from.

In the behavior script, depending on the current field value, other data fields are hidden, the row height is resized, and -- voilà.

Here is a picture of the data grid (my first test).



And here is a first script for the behavior for this first test:

Code: Select all

on LayoutControl pControlRect, pWorkingRect
   local theFieldRect
 // Behavior script of the Data Grid with row heights set for adjusting height
 // There are the fields "label", "id", "desc" and "grp".
 // There is one background graphic "background"
   
   set the top of field "id" of me to the bottom of field "label" of me + 5
   put the right of field "id" of me into item 1 of theFieldRect
   ---
   set the height of field "grp" of me to the height of field "id" of me
   set the top of field "grp" of me to the top of field "id" of me
   set the right of field "grp" of me to item 3 of pWorkingRect -5 -- Dynamic
   ---
   set the height of field "desc" of me to the height of field "id" of me
   set the width of field "desc" of me to the left of field "grp" of me - the right of field "id" of me - 10
   -- set the top of field "desc" of me to the top of field "id" of me -- is done at the end
   set the left of field "desc" of me to the right of field "id" of me + 5
   ---
   if the text of field "label" of me contains "Group" then
      // This is just an example deciding criteria to define whether 
      // a row presents group titles/subgroup title, or something else such a summary sections, etc.
      // For more types of rows I would use a Switch statement.
      show field "label" of me
      hide field "id" of me
      hide field "desc" of me
      hide field "grp" of me
      set the opaque of field "label" of me to true 
      set the textstyle of field "label" of me to bold
      put item 2 of pWorkingRect + the height of field "label" of me + 5 into item 4 of pWorkingRect
      set the backgroundcolor of field "label" of me to "249,100,0" --"0,120,220"
      set the textcolor of field "label" of me to "white"
      set the width of field "label" of me to item 3 of pWorkingRect - item 1 of pWorkingRect
      set the left of field "label" of me to item 1 of pWorkingRect
      ---
   else
      show field "label" of me
      show field "id" of me
      show field "desc" of me
      show field "grp" of me

      set the textstyle of field "label" of me to plain
      set the backgroundcolor of field "label" of me to empty
      set the textcolor of field "label" of me to empty
      set the width of field "label" of me to item 3 of pWorkingRect - item 1 of pWorkingRect
      set the left of field "label" of me to item 1 of pWorkingRect
      ---
      set the height of field "desc" of me to the formattedheight of field "desc" of me
      set the top of field "desc" of me to the top of field "id" of me -- must be reset because changing the height replaces the top of the field
   end if
   
   set the rect of graphic "Background" of me to pWorkingRect -- the background graphic defines the size of the group
end LayoutControl

P.S. For someone interested, I am also giving the script to populate the DG with test data...

Code: Select all

// Use any test button
on mouseUp
   submitTestData
end mouseUP

command submitTestData
   // These are just some test data for the local data grid named "dgDetail"
   local tData
   local tArray
   put "Pos 0.0,0,1,0" into line 1 of tData
   put "Group Material 1,2,,1" into line 2 of tData
   // Since a description field can be one or more lines,
   // we are replacing the CR/return character with "¬"
   put "Pos 1.1,1,Text 1.1¬Subtext 2.1.2¬Subtext 2.1.3,1" into line 3 of tData
   put "Pos 1.2,2,Text 2.1,1" into line 4 of tData
   put "Pos 1.3,3,Text 3.1,1" into line 5 of tData
   put "Group Services 2,5,Text 5,2" into line 6 of tData
   put "Pos 2.1,1,Text 2.1,2" into line 7 of tData
   put "Pos 2.2,2,Text 2.2,2" into line 8 of tData
   ---
   repeat with i = 1 to the number of lines of tData
      // The array contains the names of fields as keys within the DG of type FORM
      put item 1 of line i of tData into tArray[i]["label"]
      put item 2 of line i of tData into tArray[i]["id"]
      // An array can keep paragraphs with CR/return, so we replace the "¬" character
      put replaceText(item 3 of line i of tData,"¬",CR) into tArray[i]["desc"]
      put item 4 of line i of tData into tArray[i]["grp"]
   end repeat
   ---
   set the dgData of group "dgDetail" to tArray
end submitTestData

[dunbarx]

Excellent. Great job.

Craig

[golife]

Craig, thank you.

So, grouping and sub-grouping rows with a group header and summary is possible within the DG. A "row" here is just a visual space with a position in a Data Grid (DG). Such row can contain anything, so it can also contain headers, summaries, etc.

My main goal is a highly sophisticated order entry form used for hierarchically structured products and product groups allowing for order calculations, quotations, offers, invoices, etc. My backbone and data-storage is a SQL database (I am using MySQL and SQLite).

The next requirements:

• PRINTING: Print the order form using a separate print stack calculating the margins and number of pages including header, footer, first page header, last page footer, group headers and group summaries (with subgroup headers and summaries) plus a total summary section. The main problem to me seems to be to calculate how rows (= groups of items) fit on each page allowing page breaks inside of such item groups and avoiding that pages end with a label or start with a summary or last line ('orphans' and 'widows' in typesetting terminology). i have done this partially. The main concept here is that "rows" are groups of items that are placed one after the other on a card with the dimensions of the page. When the space for printing is not sufficient then a next page is created on the fly. The result is a nice printout to PDF or a printer.

• REORDERING: Allow the user to reorder rows/items groups within a FORM-DG's (Data Grid of type "form") that uses alternating row heights dragging them (or a group of them) to the desired new location inserting them there. An insertion line should be visible. So, the DG must scroll when the user moves a row/rows up or down and update visible rows on the fly. Also it shall be possible to cut rows/groups of rows and insert them at any other place within the Form DG. (Maybe this is already existing somewhere?)

[marksmithhfx]

Here is a picture of the data grid (my first test).



And here is a first script for the behavior for this first test:

Hi, this looks very interesting and I am interested in doing something similar but I'm not sure I understand what you've just done. So let me ask a question. Do you think this would work for something like searching a calendar or to-do list for a specific topic (in an Sqlite database) and then when presented, be able to categorize the output by the results of another field (say date). I am going to use my Calendar app as an example of what I would like to be able to do. In this image (zipped) I am doing a search on "CCDSS" and in the display it is putting a header break every time the date changes (so we get output for one field in the db, and the sub categories (date) based on changes in another field. So, each time the date changes (from Feb 17, to 22nd to Mar 9th) the list provides a divider break based on the change in the new date value. Do you think your technique would be applicable to something like that?

Cheers and thanks,
Mark

(sorry I had to compress the image)

[bogs]

Mark (and anyone else that comes along later), if you want to post an image that is too large for the forum, just upload it to postImage and grab the code for a thumbnail. You don't need an account or anything, so it makes a good solution.

[golife]

@marksmithhfx

Dear Mark, thank you for the picture. I made a screenshot and attach it to this message. Your date-time-item list looks very nice.

Working with data in a database in one thing. What you actually export to LiveCode may already be prepared on the database level or your LiveCode Server / PHP ... server app/s.

Your SEARCH field is the entry for searching the database. It has nothing much to do with the Data Grid (DG).

So, let us assume that as the result of your search you are receiving data in the form of XML or JSON or import data whatever other way and manage them on the client level (your desktop or mobile application.)

XML/JSON already are structured hierarchical lists, and the Array in LiveCode also is suitable allowing for hierarchically structured data. This we should not confuse with the way SQL is storing and managing data -- mainly meant to keep data separate (your database design). Possibly certain calendar data or hierarchical data are better stored and managed in so called NoSQL databases. Whatever you are using, you can definitely select the data you need.

So, the main thing for you is to receive the data in such a way that you know that at a certain date there are some items in the "group" of that date. You would sort by date and below sort items by starting time listed for that date. In SQL each item will already have a date and such time entries. Therefore, you would ORDER BY or manipulate using LiveCode script that for all items of one date the header for that date group will be that date.

Now, since the date in the original is part of the item, you would have to separate it out into it's own "label" either again on the database level or through your LiveCode script.

Let us look at the DG, and I assume that you worked with the FORM style of the DG (look at the LiveCode lessons, as I did) the rows are just containers.

1. There are rows with dates such as WEDNESDAY, FEB 17
2. There are rows with items such as "CCDSS Enhancement" or whatever
3. There seems to be a starting and end time
4. Rows below a date have two lines with a description and a time field.

So, this information is put into the array of the DataGrid "dgData" using a first key "i" -- an integer -- that indicates the row number. The second level are keys such as the name of the field "date", field "time1", field "time2", field "desc1", field "desc2" You will also need some information that you know which of the array records indicates that it is used as the label of the date, or it is the item of a date group. This can be user defined property in the date field, or a separate field with a "true" or "false" value.

Your array for the dgData of your DG group. This needs to be filled with your sorted data.

Code: Select all

// i = 1 to n equals row 1 to n
// Since the date header is a separate row, this header row has to be created before inserting it here
// It can be done on the database level or in LiveCode script. The date field nevertheless is part of
// each row, just in the item rows the date field is invisible
tArray [i]["date"] -- Field date (formatted)
tArray [i]["desc1"] --Field description
tArray [i]["time1"] -- Start time
tArray [i]["desc2"] -- Whatever, seems to be reference to a company
tArray [i]["time2"] -- Ending time
tArray [i]["rowtype"] -- Field with the value true or false
---
// After filling this array, set the dgData of group "<your DG group name>" to tArray

Let us look at the rows you want to display:

• Row 1: Only show the date. It can be copied from the date field to the label field, or you can simply only show the date field and hide all other fields. Field "rowtype" = true

• Row 2: Hide the label and date field. Show the fields Description, time, Show your first entry for this date. Field "rowtype" = false.

• Row 3: Hide the label and date field. Show the fields Description, time, Show your second entry for this date. Field "rowtype" = false.

• Row 4: Only show the date again for the next group. Field "rowtype" = true

• Row n: ...

How the row display, either as a header (the date) or as items (with times) in this example depends on the value "true" or "false" in the "rowtype" field, and you need to script this in the behavior script as I did yesterday and showed the script.

So, all you want is perfectly possible. If you want to change the color of your vertical bar lines or put some other background color of items, again, in the behavior script you can define this for each row depending on certain criteria that you have to program.

As I am anticipating that some readers here are new to LiveCode or not too familiar with the language, I am trying to describe as much as possible not assuming a lot of prior understanding. What one should know for working with DG's ("data grids" or "datagrids") in LiveCode: How to work with arrays in LiveCode, know about LC groups and how they work and how to edit them, and last but not least understand behaviors. The DG is just a complex group of groups. It has an API (application programming interface) allowing you to interact with it, and there are editing possibilities using the Property Inspector of the DG. When you choose the Form style of the DG, you can go to the template group form (this contains your icons, fields, bars, widgets, whatever) and arrange them there. The template is needed for the DG to copy it and it's controls/objects to the rows when there is data to be presented in the rows. The template for each DG is in a separate stack. Opening it first, you will not understand what is happening there and what you should do now. There is a field "label" which is not really visible, and a graphic "background" also empty. Go through the LiveCode Lessons to get an idea. Also from the Property Inspector you have access to the behavior script. In this script you define which fields/controls of your row to show or hide, change their color or other dependencies and how they display.

.

[marksmithhfx]

Mark (and anyone else that comes along later), if you want to post an image that is too large for the forum, just upload it to postImage and grab the code for a thumbnail. You don't need an account or anything, so it makes a good solution.

Great, thanks Bogs

[marksmithhfx]

@marksmithhfx

Dear Mark, thank you for the picture. I made a screenshot and attach it to this message. Your date-time-item list looks very nice.

.
.
.

So, all you want is perfectly possible. If you want to change the color of your vertical bar lines or put some other background color of items, again, in the behavior script you can define this for each row depending on certain criteria that you have to program.

Thank you GoLife. That's a beautiful explanation and I look forward to digging into it to try the ideas you have suggested. Actually, your explanation is brilliant. I would have never thought such a thing was possible with the DG but you have presented it perfectly.

Cheers,
Mark

[marksmithhfx]

Mark (and anyone else that comes along later), if you want to post an image that is too large for the forum, just upload it to postImage and grab the code for a thumbnail. You don't need an account or anything, so it makes a good solution.

Hmmm, might be a problem with that. I just had an image posted this way in another forum that went missing. viewtopic.php?f=49&t=35616

[bogs]

Mark (and anyone else that comes along later), if you want to post an image that is too large for the forum, just upload it to postImage and grab the code for a thumbnail. You don't need an account or anything, so it makes a good solution.

Hmmm, might be a problem with that. I just had an image posted this way in another forum that went missing. viewtopic.php?f=49&t=35616

That can (and does) happen regardless of how the picture was posted. I've had images I uploaded through the forum go missing as well, however, I mentioned using an outside server to host the image mainly to address the issue that caused you to zip the image, i.e. the image you had was too large for the forum to upload.

Some things of note when it comes to images and forums (and hosting sites) -
1. no matter where you upload it, an image can be lost due to a number of things, such as a server refresh, move, update, etc.
2. if you get a note saying your image is too large, you can scale the image to 50% and try again. If it is still too large, repeat until it is not, or crop the picture instead to what is absolutely necessary to convey the problem.
3. using a hosting service (such as post image) will allow the posting of the image now, which is usually the more important consideration over longevity.