Use Variables in Partial Reload

[jzimolong]

I've been trying many different things based on research in the community, but figured I would post my own question on this.

I have three variables, which I set the values as follows:

vSelectedVer:   =GetFieldSelections(ver,', ',20)

vFirstVer:         =SubField(vSelectedVer,', ',1)

vSecondVer:    =SubField(vSelectedVer,', ',2)

I have a field "ver" in my app, and a corresponding listbox with following values:

20140930

20140831

20140731

20140630

I want user to select two of the values.  So if they select 20140930 and 20140630, the variables look like this:

vSelectedVer:     20140930, 20140630

vFirstVer:           20140930

vSecondVer:      20140630

I can use these in charts, part of set analysis to do different displays and calculations.

What I have now been trying to do is use these values in a REPLACE LOAD script.  I want to run partial, so I can pull a subset of the RESIDENT table, and then I can bring the subset in to make a pivot chart.  The resident table carries a portfolio of accounts, each with a different "ver" or monthend identifier.  Here is what my REPLACE LOAD script looks like.  I haven't made it complex, as I'm just trying to get it to work using variable first.

PDMigration:

REPLACE LOAD

  ver

  , RootObgNo

  , RootOblNo

  , PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = $(vFirstVer) ;

Whenever I try it, I get the following script error:

Field not found - <=>

PDMigration:

REPLACE LOAD

  ver

  , RootObgNo

  , RootOblNo

  , PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = =SubField(vSelectedVer,', ',1)

[vgutkovsky]

Joseph, I think the easiest way to accomplish what you need is to create a temporary variable in your script. Try this:

LET vFirstVerB = replace(vFirstVer,'=','');

PDMigration:

REPLACE LOAD

  ver

  , RootObgNo

  , RootOblNo

  , PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = $(vFirstVerB);

LET vFirstVerB = null();

[jzimolong]

Thanks for the suggestion.  I made the updates and I still get the following error in the script:

Field not found - <vSelectedVer>

PDMigration:

REPLACE LOAD

  ver

  , RootObgNo

  , RootOblNo

  , PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = SubField(vSelectedVer,', ',1)

[vgutkovsky · Accepted solution]

My mistake, the above won't work for your type of variable expression. The only way (that I can see) to do it is through adding triggers. In your case, open Document Properties >> Triggers >> Field Event Triggers >> ver. Press the "Add Action" button under OnSelect. Press Add >> External >> Set Variable. For the variable name, enter vFirstVerB. For the variable value, enter =vFirstVer

Create the same trigger for OnChange.

Then your load script can be as I wrote above except for setting vFirstVerB.

PDMigration:

REPLACE LOAD

  ver

  , RootObgNo

  , RootOblNo

  , PDGrade as CurPD

RESIDENT vCRS_RLD

WHERE ver = $(vFirstVerB);

It's not very elegant, but I honestly can't think of another way of doing it.

Vlad

[jzimolong]

Thank you Vlad, this appears to be working!  I can now further expand the script.  Thanks again, this gets me over the first hurdle.