Jump to content

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


This topic is 2803 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

Screen Shot 2016-08-21 at 23.27.39.png

Edited by fm8443
Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

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.

 

  • Like 1
Link to comment
Share on other sites

This topic is 2803 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.