Doing Master detail in the best way..

[cdsaenz]

Programming can pose the same doubts again and again.
Now back on the clipper area, I'm trying to write some application and I'm wondering about the best way to handle a master detail edition form.

Normally I will add just the textboxes for the master and below a grid for the detail. I have a couple of options:

1) Have the grid directly attached to the detail table, and edit this "live". I guess the only way to limit the detail records is to use the old SET FILTER command. Ain't this an inneficient command like LOCATE? Or does it use indexes to be faster?

2) Have the grid editing an array which is a copy of the records in the table. Save them back to the table when hitting "OK" (probably deleting all the filtered detail records first)

Which of this is your preferred approach? Or maybe other (like editing a single line at a time.. not good for typists..)

Sorry for the basic questions, it's just a matter of setting some basic things to start working at full speed!

[sudip]

Hello Charly,

Thank you very much for creating this topic.

IMHO, your 2nd choice is a better choice to me. Because,
1. HMG is also used with SQL Databases.
2. Even for using dbf tables, corruption will be less when you store your data from a table to variables, arrays or controls and when user wants to update, store them to the tables with proper validation.
3. Your many section (in one-to-many) may need to store more than one tables also.
4. More flexible.

I prefer to store records from the table directly to controls (eg, texbox, combobox, grid etc.). This reduces some overheads, especially when you are working with large number of records in an array.

[mol]

I'm using almost only second choice, too.
Editing records in this way gives more control over inputed data.

[esgici]

Hi all

This is a interesting and important topic, thanks Charly

First: "best" is never found ! may be "better" only and may found always. This is depends first your needs and properties of files.

Normally I will add just the textboxes for the master and below a grid for the detail.

Yes, individual controls for master and grid for detail is reasonable. But first we have make a choice : does everything goes a single form ?

I guess the only way to limit the detail records is to use the old SET FILTER command. Ain't this an inefficient command like LOCATE? Or does it use indexes to be faster?

SET FILTER and LOCATE-CONTINUE are slow because of its nature, indexes are fast and preferable.

In my humble opinion, "Have the grid directly attached to the detail table, and edit this live" is better than "Have the grid editing an array which is a copy of the records in the table" in this topic. Because not required "load" and "save" fields by extra work.

And, for this issue we have consider SET RELATION.

If anyone give a simple, short and working sample, may be a good reference for all our friends.

Regards

--

Esgici

[esgici]

I'm using almost only second choice, too.
Editing records in this way gives more control over inputed data.

For control (validate) user input, array isn't only way; a temporary table (even with single record) may use.

Regards

--

Esgici

[cdsaenz]

Thanks to all of you gentlemen for this great input. I think talking about actual real life practices is important, specially when the tool has grown up and is ready to use.

To answer in general:
- I do prefer to use a "buffer" instead of the "whole" detail table being edited live. Specially when we're potentially in a networked environment.

- I like the temporary table thing Esgici!! (you're right that "better" is the word.. there's no absolute answer!! )

- Another GREAT point, Esgici.. Single form yes or not! Thing is.. I care a lot about touch typists... And they appreciate the single form if possible: to go from master to a detail grid with a single enter, and then across cells with enter, adding rows automatically as in Excel..

My conclusions so far:
- Unless I'm missing something new in Harbour, there is no way to avoid a temp-table or array if you wanna stay away from SET FILTER (which I want to). I need to investigate SQL options, but in DBF there's no way to get a queried subset, updateable or not.

- I will try to stick for now to the single form master/detail edition style.

- I'm thinking of these elements for the solution: HASHES or associated arrays (hey love that from PHP) or simple arrays, to "gather" and "scatter" a record content. I remembered that at reading an old and nice Clipper 5.x book. Either, I could build some custom function to create a TEMP DBF, gather stuff to it, and put it back to the original table.

One question..
- I've had a hard time to make the GRID control work with arrays, specially to allow addition of records.. Is that possible?

Else I'll just stick to the temp table.. it sounds even better every time

[esgici]

Hola Charly

- I do prefer to use a "buffer" instead of the "whole" detail table being edited live. Specially when we're potentially in a networked environment.

Networked or not, this is the better way in many reasons; validate user input, get user confirmation for updating data and especially open a little data set to user instead of full table. Sometime called CURSOR (Current Set Of Records) this limited detail table.

- I like the temporary table

Does we are talking about same thing ? I'm talking about temp table in C:\hmg\SAMPLES\TEMPORARY.TABLE sample.

-Single form yes or not!...they appreciate the single form if possible

Yes, this is reasonable; multiform is an alternative and is needless if isn't imperative of course.

- Unless I'm missing something new in Harbour, there is no way to avoid a temp-table or array if you wanna stay away from SET FILTER (which I want to).

Harbour or anything else (excepting relatively short table and deceptive techniques such as Rushmore Technology), SET FILTER is a slow way for limiting records. You can use or not SET FILTER for building temp-table or array; so building temp_table or array (by a way other than SET FILTER of course) is necessary.

Please consider this pseudo-code :

Code: Select all

- For a master-detail structure, it's necessary  a key field common for both file; and detail file (most probably master field too)  is indexed on this key field.

- build a temp table with same structure of detail.
- master->go top
- seek master->key_field in detail
- if found()
     while master->key_field equal to detail->key_field .and. .not eof() in detail
          append blank to temp_table
          copy fields from detail to temp_table
          skip in detail
     enddo

... in DBF there's no way to get a queried subset, updateable or not

What is queried (subset or not) ?

If this meant "building anything (a records subset, a report, a grid, a table structure, etc...) by a QUERY of SQL statement; I'm always unable to understand this approach

Does above pseudo-code is a query or not ? If so, why "no way" ?

- I will try to stick for now to the single form master/detail edition style

Good choice

-- I'm thinking of these elements for the solution: HASHES or associated arrays (hey love that from PHP) or simple arrays, to "gather" and "scatter" a record content. I remembered that at reading an old and nice Clipper 5.x book.
Either, I could build some custom function to create a TEMP DBF, gather stuff to it, and put it back to the original table.

You have doing a decision : array (any type) or temp-table ?

One question..
- I've had a hard time to make the GRID control work with arrays, specially to allow addition of records.. Is that possible?
Else I'll just stick to the temp table.. it sounds even better every time

We have dozen of sample on GRID. Certainly you may find (or develop any) to adding records.

By the way, why you don't thing BROWSE, instead of GRID ? Though it's obsolete against GRID, may having some preferable feature (such as append record)

Sorry for long speech; this is because I'm an old (that is old fashioned) man and my English is worst

I will wait eagerly your sample (short, simple and working ).

Saludos

--

Esgici

[cdsaenz]

THANKS for the long speech. All is very valuable!

- In fact... I didn't know about C:\hmg\SAMPLES\TEMPORARY.TABLE I was struggling to create a temp table name.. good to know i can add that function now THANKS!

- Your pseudo code matches EXACTLY my code. Great minds think alike

- About queries, well any SQL language will let you get "SELECT * FROM detail WHERE detail_master_key = master.key" or something. Very nice believe me. But we'll do without it.

- But back to business now, I've built a function to get my stuff to a temp table. I need the reverse now.

- About BROWSE.. After some reading here I decided to move all to GRID... Not sure now. I would just stick to one of them.. But if you say it's better for this..

One thing I noticed, I couldn't assign an alias in runtime to rowsource.. is that right? I mean I tried to do form1.grid.rowsource := cTempTableAlias and got a compiling error.. (?)

Below the code, bear with me, I'm going back to Clipper (I got carried away by aliased functions!)

Code: Select all

FUNCTION DetailToTable(cFileFrom,cKeyField,xMasterValue)

LOCAL cFileTo
/* temp file name */
cFileTo := "tmpdetail" /*DTOS(DATE()) + STRTRAN(TIME(),":","")*/

/* copy stru from original detail and open temp*/
SELECT (cFileFrom)
COPY STRUCTURE TO (cFileTo)
USE (cFileTo) NEW EXCLUSIVE

/* should be setordered */
SELECT (cFileFrom)
SEEK xMasterValue
DO WHILE (&(INDEXKEY(0)) = xMasterValue)   
   (cFileTo)->(dbAppend())
         
   FOR i:=1 TO (cFileTo)->(FCOUNT())
      (cFileTo)->(FIELDPUT(i, (cFileFrom)->(FIELDGET(i))))                      
   NEXT    
   
   (cFileFrom)->(dbSkip())   
ENDDO
   
RETURN (cFileTo)

[esgici]

Hola Charly

Thanks to nice words

If not disturbed anyone, I can speech more and more long

- .. (SQL language) Very nice believe me.

For me, no ! I don't like someone ( in fact something ) worked in place of me

But we'll do without it.

Certainly ! So, we must do !

- I've built a function to get my stuff to a temp table. I need the reverse now.

For reverse process, we have consider some situations :

- First, we have assure every (or necessary) field content validated for proper value
- How we'll implement records deleted by user in temple-table ( exists or not in original detail file )
- Records newly added by user may require extra key-field informations, such as sub-number etc
- Does user will have right of changing key field content ? If so, does we need extra cautions ?

- About BROWSE.. After some reading here I decided to move all to GRID...

Good choice

( BROWSE vs GRID) Not sure now. I would just stick to one of them.. But if you say it's better for this..

No, it isn't better nor can be. May be only preferable under some situations and we haven't any of its for now.

One thing I noticed, I couldn't assign an alias in runtime to rowsource.. is that right? I mean I tried to do form1.grid.rowsource := cTempTableAlias and got a compiling error.. (?)

Yes we could. Without your code and error message I can't say anything.

Below the code, bear with me, I'm going back to Clipper (I got carried away by aliased functions!)

Your code is very good; certainly a Clipper'ist mind and coding

Code: Select all

/* temp file name */
cFileTo := "tmpdetail" /*DTOS(DATE()) + STRTRAN(TIME(),":","")*/ 

Though commented and no need with HB_DBCreateTemp; you don't need construct a name to your temp-table such as DTOS(DATE()) + STRTRAN(TIME(). Anyway, this formula doesn't assure uniqueness of file name ( within a second may be built more than one file) Instead, Harbour have two nice function for this purpose :

Code: Select all

   TempFile( <cDir>, <cExt>, <nAttr> ) => <cTempFileName>

and

Code: Select all

   HB_FTempCreateEx( @<cFileName>, [cDir], [cPrefix], [cExtention], [nAttr] ) => <fhnd>

Please keep in mind that first function return a character value as file name and second return a numeric value as file handle. For getting file name from second function, we'll use <cFileName>; as syntax implied it must be passed by reference.

Note : I know its exist and I had already use; but for now I can't use TempFile(). Anyway this is an unnecessary detail for now, because HB_DBCreateTemp() doesn't require an UNIQUE file name. In fact the syntax of this function is :

Code: Select all

HB_dbCreateTemp( <cAlias>, <aStruct>, <cRDD>, <cCodePage>, <nConnection> ) -> <lSuccess>

while the first parameter is alias, not file name.

Code: Select all

/* copy stru from original detail and open temp*/
SELECT (cFileFrom)
COPY STRUCTURE TO (cFileTo)
USE (cFileTo) NEW EXCLUSIVE 

With this way you couldn't use HB_dbCreateTemp. May be like this :

Code: Select all

SELECT (cFileFrom)
aTempFileStru := DBSTRUCT()
cTempFileAlias :=  "TempDetail"  

if !HB_dbCreateTemp( cTempFileAlias, aTempFileStru )                // Unsuccessful
    MsgBox( "Cannot create temporary table: " + cTempFileAlias )
     ...
endif

Code: Select all

/* should be setordered */
SELECT (cFileFrom)
SEEK xMasterValue
DO WHILE (&(INDEXKEY(0)) = xMasterValue)   

Perhaps you are thinking somethings like this, when your code will put to run:

(After assigned a value to <nOrder>)

Code: Select all

SELECT (cFileFrom)
DBSETORDER( nOrder )  //  or SET ORDER TO nOrder
SEEK xMasterValue
DO WHILE (&(INDEXKEY(nOrder)) = xMasterValue)   

( Order 0 (zero) meant natural order. )

Though this is a statement correct in syntax, calling a function inside of conditional statement doesn't seem to me as a good way. Instead may think :

Code: Select all

cDetailIndxKey := INDEXKEY(nOrder)
DO WHILE (cDetailIndxKey) = xMasterValue)  

( Caution : not tried :( ) 

Code: Select all

   FOR i:=1 TO (cFileTo)->(FCOUNT())
      (cFileTo)->(FIELDPUT(i, (cFileFrom)->(FIELDGET(i))))                      
   NEXT    

Very nice

I'm still waiting your "working" sample

Saludos

--

Esgici

[cdsaenz]

Whoa a couple of great ideas here. Will try all. Hey! I didn't have any idea about DBSTRUCT().. I think I'll have to get back to basics!!

In fact I've changed a little the code.. I don't think it's practical to have a function trying to do it all.. Specially when the index key for the detail surely will be compound, the code I made won't work..

I've figured out something that seems to be working so far: a function that creates the temp file, other that loads the current content (if any) to the temp table and another one that saves back to the real DBF.

The issue now... Tried Browse but didn't work (let's leave it for afterwards...) so got back to Grid. Ok but... it seems that the Grid saves the edit in a buffer. So I need to figure out a way to save the buffer to the temp table.. I don't even need that but not sure if I can avoid it. I tried the Save() method of the Grid but it only saves the current record... and there is no even to hook to.. What am I missing?

PS I think I'll build some test code because I'm building something bigger and it's harder to test..

Code: Select all

DEFINE GRID GridMaster
        ROW    130
        COL    30
        WIDTH  450
        HEIGHT 200
        ITEMS NIL
        VALUE {1,1}
        WIDTHS { 150,150}
        HEADERS {'Acct','Amount'}
        FONTNAME "Arial"
        FONTSIZE 9
        TOOLTIP ""
        ONCHANGE nil
        ONGOTFOCUS Nil
        ONLOSTFOCUS Nil
        FONTBOLD .F.
        FONTITALIC .F.
        FONTUNDERLINE .F.
        FONTSTRIKEOUT .F.
        ONDBLCLICK Nil
        ONHEADCLICK Nil
        ONQUERYDATA Nil
        MULTISELECT .F.
        ALLOWEDIT .T.
        VIRTUAL .F.
        DYNAMICBACKCOLOR Nil
        DYNAMICFORECOLOR Nil
        COLUMNWHEN Nil
        COLUMNVALID Nil
        COLUMNCONTROLS {{"TEXTBOX","CHARACTER"},{"TEXTBOX","NUMERIC","9999999.99"}}
        SHOWHEADERS .T.
        CELLNAVIGATION .T.
        NOLINES .F.
        HELPID Nil
        IMAGE Nil
        JUSTIFY Nil
        ITEMCOUNT Nil
        BACKCOLOR NIL
        FONTCOLOR NIL
        HEADERIMAGES Nil
        ROWSOURCE "TempDetail"
        COLUMNFIELDS {"acc_nbr","line_amt"}
        ALLOWAPPEND .T.
        ALLOWDELETE .T.
        BUFFERED .F.
        DYNAMICDISPLAY Nil
        ONSAVE nil
        LOCKCOLUMNS 0
    END GRID