Looping through record elements.

[Ben R.]

Is there a way to programmatically loop through the elements of a record? I'd like to be able to compare, element by element, two record rows. I thought it might work to treat them like multidimensional arrays but that didn't work.

This is what I'm going for, basically:

for i = 1 to record[arr_curr()].getlength() //Dynamically detect the number of elements in the record
  if record[arr_curr()]. != record[arr_curr() -1]. then //Compare matching elements between the two rows.
    display "Elements are not the same!"
  end if
end for

[Sebastien F.]

Hello,
Genero supports record comparison with the .* notation.
Have a look at this pages:
https://4js.com/online_documentation/fjs-fgl-manual-html/?path=fjs-fgl-manual#c_fgl_operators_EQUAL.html
https://4js.com/online_documentation/fjs-fgl-manual-html/?path=fjs-fgl-manual#c_fgl_operators_DIFFERENT.html
https://4js.com/online_documentation/fjs-fgl-manual-html/?path=fjs-fgl-manual#c_fgl_records_006.html
Seb

[Ben R.]

I realize that, but I'm trying to figure out a way to compare each element individually without having to write out each comparison. Otherwise with a record that has fifteen elements I'd have to do this:

if record[arr_curr()].firstelement != record[arr_curr() -1].firstelement then
  do this
end if
if record[arr_curr()].secondelement != record[arr_curr() -1].secondelement then
  do this
end if
etc.

The reason for wanting to dynamically address both the record element length and to cycle through each one is because we need to log data changes in a input array. The end goal would look something like this:

define
  originalRecord record like recordArray[1].*

  input array recordArray from screenArray
    before row
      let originalRecord = recordArray[arr_curr()].*
    after row
      call compareRows(originalRecord, recordArray[arr_curr()])
  end input

function compareRows(originalRecord, currentRecord)
  define
    originalRecord record,
    currentRecord record,
    n integer

   for n = 1 to originalRecord.getlength()
     if originalRecord.[n] = currentRecord[n] then
       //code to log change in database
     end if
   end for
end function

If a program has five tables with fifteen elements each that'd be 45 sets of repeated code. We have a number of programs that we need to do this for and having to explicitly write out every single record element comparison is very wasteful. If the above approach isn't something that can be done in Genero, is there an alternate you'd suggest for achieving the same thing?

[Sebastien F.]

Before we discuss the implementation solution, may I suggest that you give more details about why you need to log changes in an input array?
Is this just to track user changes or update the database or ... ?
What version of Genero are you using?
That would also help to propose solutions.
Seb

[Ben R.]

This is to track user changes in our database, yes. We've got a number of products and customers and vendors and such that need to be writable by our internal employees but we also want to be able to look at an item's history if something comes up.

fgl_getversion reports 1169.85, if that's what you're looking for.

[Sebastien F.]

fgl_getversion = 1169.85 means it's a 2.11.05 (provide fglrun -V next time please).
2.11.05 is from end  2008 ...
There have been a lot of enhancements since 2.11, so first I would strongly suggest to upgrade to 2.50, or better, try to participate to the upcoming EAP of Genero V 3.00 ...

Then, regarding user change tracking, what granularity to you want to achieve? Do you want to track any single column value change? Keeping history of all changes in the database? or do you just want to track the last user id and the time when the row was changed? In the last case I would just add two columns in each table, to have a modification timestamp (DATETIME YEAR TO FRACTION(N)) and the user id who did the last change. You can easily setup these field/column values when the user changes a row in an INPUT ARRAY (see ON ROW CHANGE trigger).

Note also that some database servers provide such feature, assuming that each physical user is identified by a real DB user.

[Ben R.]

Yes, here's the fglrun -V output:

fglrun 2.11.05 build-1169.85
Built Oct 30 2008 12:57:49
(c) 1989-2008 Four J's Development Tools

I'm not sure why we're on 2.11 instead of 2.50. We have a 3rd party company do those upgrades so I've no idea what is involved. I think we just haven't had them do it since 2.50 was officially released.

We have a table that we insert changelog records into. It contains the unique keys to identify the record, the name of the able, date, time, agent, and a description of the change. We don't need to maintain a changelog for every table or field, just select ones, so a full database changelog isn't necessary.

On Row Change is what I had intended to type in as the trigger for the compareRows function when doing the code in my earlier post.

[Reuben B.]

Versions

All our tools should return the appropriate version details when called with the -V argument

Upgrading

I'm not sure why you need a 3rd party company to look after your upgrades.  There have been many upgrades since 2.11.  All the documentation for our products should have two sections, a New Features section which lists the new functionality we have added (e.g. for the language https://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_nf.html), so you are missing out on 7 upgrades and 7 years worth of new features.  There is also an Upgrade guide which lists any code changes you may have to apply to your code, sometimes we have to break things in order to move forward (e.g. for the BDL https://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_upgrade_guides.html).  I describe these two sections, the New Features is the fun stuff to read, whilst the Upgrade Guide is the mandatory stuff to read.  Also remembering that GDC, GAS, GRE etc have their own documentation with equivalent sections

You should also be aware that any day now, we start the Early Access Program, (or Beta phase) for the next release 3.0 of our products  http://4js.com/genero-version-3-0-preview/

Your Request

I understand your request, I think I have requested it in the past when I was a customer.  There is a new feature in 3.0 called Generic or Dynamic Dialogs.  I believe something like what you have requested maybe beneficial there.  However there is a way I believe you can get what you want using what is currently available. 

That is to use the base.TypeInfo.create() method to convert the record into a DomNode, and do a diff on the resultant DomNode and its child elements and attributes.  Have a look at the following code ...

Code (genero) Select Expand

MAIN
TYPE coordType RECORD
    lat, lng DECIMAL(9,5)
END RECORD

DEFINE x1, x2 coordType
DEFINE x3 STRING
DEFINE x4 RECORD
lng, lat DECIMAL(9,5)
END RECORD

    LET x1.lat = 10
    LET x1.lng = 10

    -- Test for same
    LET x2.* = x1.*
    CALL record_diff (base.TypeInfo.create(x1),base.TypeInfo.create(x2))
   
    -- Test for different structure length
    CALL record_diff (base.TypeInfo.create(x1),base.TypeInfo.create(x3))

    -- Test for different structure elements
    CALL record_diff (base.TypeInfo.create(x1),base.TypeInfo.create(x4))

    -- Test for same structure different values
    LET x2.lat = 20
    CALL record_diff (base.TypeInfo.create(x1),base.TypeInfo.create(x2))
END MAIN

FUNCTION record_diff(r1,r2)
DEFINE r1, r2, f1, f2 om.DomNode
DEFINE i INTEGER

    -- Check length
    IF r1.getChildCount() != r2.getChildCount() THEN
        DISPLAY "Record length is different"
        RETURN
    END IF

    -- Check record structure is same
    FOR i = 1 TO r1.getChildCount()
        LET f1 = r1.getChildByIndex(i)
        LET f2 = r2.getChildByIndex(i)
        IF f1.getAttribute("name") != f2.getAttribute("name") THEN
            DISPLAY "Record structure is different"
            RETURN
        END IF
        IF f1.getAttribute("type") != f2.getAttribute("type") THEN
            DISPLAY "Record structure is different"
            RETURN
        END IF
    END FOR

    -- Record structure is same, check values
     FOR i = 1 TO r1.getChildCount()
        LET f1 = r1.getChildByIndex(i)
        LET f2 = r2.getChildByIndex(i)

        IF f1.getAttribute("value") != f2.getAttribute("value") THEN
            DISPLAY SFMT("Element %1 has different values %2 != %3",f1.getAttribute("name"), f1.getAttribute("value"),f2.getAttribute("value"))
            RETURN
        END IF
    END FOR
    DISPLAY "Record elements are the same"
    RETURN
END FUNCTION

That should run in 2.11, base.TypeInfo.create was added in 1.33


Auditing

I will point out though that if the purpose is to audit changes to certain fields, and record who changed them etc, this approach requires you being disciplined in your coding so as to ensure that every change is captured.  This typically means routing calls through a single INSERT/UPDATE/DELETE, and not having any other access to your database.  Another approach is to use database triggers to write to an audit table on every insert, update, delete, and these triggers can normally be generated from a database schema.

Hope that helps,

Reuben

[Ben R.]

The reason for the 3rd party upgrades is because we're using fourthgeneration's code generator for consistency with our legacy code. It's been in use here for around 25 years I think. It's grown increasingly out-of-date as they've moved to selling their own products and away from maintaining the code generator. Due to that we have to juggle a bit to make sure that upgrades don't break anything. I did try GDC 2.50 when it came out IIRC and that caused problems with all our top menu buttons getting copies at the bottom of the screen. I imagine at least some of the 2.50 upgrades would require the accompanying GDC update, so that's a problem. I've no idea if there would be other issues with upgrading, though there's certainly plenty of things in the 2.11+ upgrades that I'd find beneficial.

I did try a small test using a variation of the code you suggested. It worked, but I ran into an issue with comboboxes. Here's what I ended up doing to test:

  define
    originalRow om.domnode,
    cellData om.domnode

  input array p_freight from s_dfreight.*
    before row
      let originalRow = base.typeinfo.create(p_freight[arr_curr()])
      let cellData = originalRow.getchildbyindex(3)
      display cellData.getattribute("value")

  end input array

This correctly displays the value for edit boxes, but for a combobox grabbing the value doesn't directly allow for getting the combobox item text. So for a dropdown with item (1, "Dollars") for example value only retrieves the 1. Any suggestions on how to access the column type, and if it's a combobox then access the item text from that point? I think that's the only thing I'm still stuck on that prevents me from being able to dynamically browse a table row and then compare and subsequently log field changes.

[Reuben B.]

This correctly displays the value for edit boxes, but for a combobox grabbing the value doesn't directly allow for getting the combobox item text. So for a dropdown with item (1, "Dollars") for example value only retrieves the 1. Any suggestions on how to access the column type, and if it's a combobox then access the item text from that point? I think that's the only thing I'm still stuck on that prevents me from being able to dynamically browse a table row and then compare and subsequently log field changes.

You can use the ui.ComboBox get* methods https://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_ClassCombo_methods.html to retrieve the value.  However for audit/log purposes it is likely you would want to log the database value, rather than the displayed value.

You may also want to look at my AUITree example https://code.google.com/p/sourcefourjs/wiki/auitree, that may help if their isn't a readily available Genero method to do things such as accessing the column type.

[Ben R.]

This approach worked:

Code Select Expand


function compare_input(originalInput, currentInput)
  define
    originalInput om.domNode,
    currentInput om.domNode,
    originalCell om.domNode,
    currentCell om.domNode,
    dropDown ui.combobox,
    i integer

  for i=1 to originalInput.getchildcount()
    let originalCell = originalInput.getchildbyindex(i)
    let currentCell = currentInput.getchildbyindex(i)
    let dropDown = ui.combobox.forName(originalCell.getAttribute("name"))

    if originalCell.getattribute("value") != currentCell.getattribute("value") then
      if dropDown is null then
        let scratch = originalCell.getattribute("name"), " changed from ",
          originalCell.getattribute("value"), " to ", currentCell.getattribute("value"), "."
      else
        let scratch = originalCell.getattribute("name"), " changed from ",
          dropDown.getitemtext(originalCell.getattribute("value")), " to ",
          dropDown.getitemtext(currentCell.getattribute("value")), "."
      end if
      call fgl_winmessage("",scratch,"")
    end if
  end for
end function


Of course this only works as long as neither cell value is null. I'll have to figure out how to get the logic to accommodate that possibility. Once I've gotten that figured out I will replace the fgl_winmessage stuff with logic for handling the database insertions for the changelog.