Conditional lookup

Scriptcase 8 Discussion
#1

Thought that I had this working
http://www.scriptcase.net/forum/forum/applications/forms/70786-solution-use-a-global-variable-in-lookup-based-on-value-of-another-field

But - it is retaining the last value selected and I cannot figure out how/where to reset the value of those global variables. It does write the appropriate values to the database - but in SC, when I go back and look at the record later, those fields are empty. If I change one record then the next record that I look at, the fields are empty and the lookup is displaying the values selected the last time that i used it (Hope that makes sense).

Here’s what I want to do:
Select a transaction type (for example “deposit” or “withdrawal”)
Depending on the transaction type selected, change the lookup for account1 and account2 - so that only the accounts that have the same account_type are listed (for example - a deposit should use an “income” account and “bank” account - but a withdrawal should use a “Bank” account and “Expense” account)
Select the appropriate account (if Deposit; account1 = “Income”, account2 = “bank” - but if Withdrawal; account1 = “Bank” and account2 = “Expense”)

Have tried to reset the variable in every event - and have found that if I reset the variable then I don’t get a lookup list at all.

#2

Hi Betty.

Maybe it’s a “timing” issue. If you set the global variables in the onChange AJAX event… what values the globals have when you change to another record? I think you need to somehow set the value for $acct1_type and $acct2_type also in the onLoadRecord event.

Can you try this?

#3

"The OnLoadRecord event is not available when the orientation is defined as single record … "

I’ve taken a different approach … (it sure would be handy to know if I am doing things the ‘right’ way! - seems a little convoluted but it works)

  1. Created a GRID for lookup of each account (need one for each account field) with a where clause using a global variable SELECT
    account_id,
    account_type,
    account_description,
    active
    FROM
    account
    WHERE account_type = ‘[acct_type1]’ AND active = ‘Y’

  2. AJAX Event “transtype_onChange” on my data entry FORM (this sets the global variable)
    switch({transtype}) {
    case ‘Transfer’:
    [acct_type1] = “Bank” ;
    [acct_type2] = “Bank” ;
    break;
    case ‘Deposit’:
    [acct_type1] = “Income” ;
    [acct_type2] = “Bank” ;
    break;
    case ‘Withdraw’:
    [acct_type1] = “Bank” ;
    [acct_type2] = “Expense” ;
    break;
    Etc…

  3. Capture link on the data entry FORM (one for each account field- account1 & account2)

  4. NO changes to the account fields - these are simple text fields - the user can type anything they want into them - restricted only by allowed characters

  5. OnValidate event to verify that accounts match the defined rules sc_lookup
    switch({transtype}) {
    case ‘Deposit’:
    sc_lookup(my_account, “SELECT account_type FROM account WHERE account_id = ‘{account1}’”);
    {acct_type} = {my_account[0][0]};
    if ({acct_type} != “Income” ) {
    sc_error_message(“Account1 {account1} is not valid. Account type must be Income.”) ;
    }
    break;
    case ‘Withdraw’:
    sc_lookup(my_account, “SELECT account_type FROM account WHERE account_id = ‘{account1}’”);
    {acct_type} = {my_account[0][0]};
    if ({acct_type} != “Bank” ) {
    sc_error_message(“Account1 {account1} is not valid. Account type must Bank.”) ;
    }
    break;
    }

#4

Betty: You’re right about the onLoadRecord event… yes, you’re solution is a little convoluted, but I think is a very good one. Sometimes we all need to do some patch work to make Scriptcase behave, ha ha…