Update COUNTRY rows based on it's previous non-blank setting

[fm8443]

I have a Layout in Table Mode as (partially) shown in the attachment.
(Note:  First row will have a Country Code in it - not shown in attachment)

I want to "fill-down" the last country code in all rows (in the Country Field) UNTIL the country code changes.  Then I want to similarly do it for that one.  Until I reach the end of rows.

My database will grow from 500 to 10,000 rows of text/number data.  I am uploading in batches from Excel.

Is this possible?  I've tried various calculations and scripting without much success.  Thanks!

I'm trying to get something like this:

COUNTRY
IT
IT
IT
IT
IT
IT
IT
IT
ES
ES
ES
ES
ES
AU
AU
AU
AU
AU
IN
IN
IN
IN
.......etc

 

Edited August 21, 2016 by fm8443

[comment]

Try a script like this:

Go to Record/Request/Page [ First ]
Loop
    If [ not IsEmpty ( YourTable::Country ) ]
        Set Variable [ $country; Value:YourTable::Country ]
    Else
        Set Field [ YourTable::Country; $country ]
    End If
    Go to Record/Request/Page [ Next; Exit after last ]
End Loop 

 

Make sure you have a backup. There is no undo on this.

 

 

Edited August 21, 2016 by comment

[fm8443]

This works very well.  Thanks for the help.

I now have this as a Script - that I will have to remember to run manually.  Is it possible to make my COUNTRY field a "Calculation" field and add this script into the actual CALCULATION? - so that it auto-calculates whenever I import new rows?  Or is there a way to auto-trigger this script when new rows are imported? 

[webko]

Calculation - no. As this script relies on a found set, and an initial value, this would give inconsistent results.

Script - make your import also a script, and then run this after the Import

[comment]

3 hours ago, fm8443 said:

Is it possible to make my COUNTRY field a "Calculation" field

You would not want it to be a Calculation field - because that would show empty or wrong values if you changed your found set or sorted your records.

In theory, it would be possible to make it a Text field with auto-entered calculated value. In practice, that would require a complicated custom function. Which would run exactly once per record. And you would have to remember to remove it in future versions of your solution, after you have imported all your records.

As webko suggested, make it a a part of your importing script.