LiveCode Forums.

Posted: **Mon Jun 22, 2009 1:35 am**

Hi,
I'm continuing the conversion of my spreadsheets in Revolution

In the spreadsheet I have this:field E5 where I enter the number of storeys
and field M13 where the formula makes the value searched in the table (http://picasaweb.google.com/pannellirad ... 6660325858) through the formula "= VLOOKUP (E5, L39: M78, 2)"

In Revolution I made in the relevant fields (field "Storey" and field "ValueStorey") and table (I use tablefield with two columns: column "Storey" and column "Value") but to write the script I do not know where to start.
How can I do?
Thanks.

Posted: **Mon Jun 22, 2009 6:32 am**

You can do this using the 'lineOffset' function, and the knowledge that columns are separated by tabs. Assuming that you're using a DataGrid object:

Code: Select all

on mouseUp 
   put the dgText of group "StoreyValueGrid" into tText
   put lineOffset(field "Storey" & tab, tText) into tLineNumber
   if tLineNumber > 0 then
      set the itemDelimiter to tab
      put item 2 of line tLineNumber of tText into field "ValueStorey"
   else
      answer "Could not find it"
   end if
end mouseUp

By appending the tab delimiter to the first parameter of the lineOffset function, we ensure that we get exactly that line, and not another line that happoens to start with the same characters (think: 'foo' and 'foobar' start with the same 3 characters, but you really want 'foo' and not 'foobar' when you look for 'foo')

HTH,

Jan Schenkel.

Posted: **Mon Jun 22, 2009 2:44 pm**

Hi Jan,

I have tried with your script (I also tried to change it) but does not work

Sorry my description was unclear

This is my stack:

Button "Calculate"

field "Storey" where I put the number of storey

field "ValueStorey" where the script puts the corresponding value of the number of storey

Datagrid "StoreyValueGrid" composed of a column "Storey" and column "Value" where the script should check the value entered in the "Storey" and put corresponding value in the "ValueStorey"

This is the stack:http://picasaweb.google.com/pannellirad ... 6801103154

Thanks in advance

Posted: **Mon Jun 22, 2009 7:43 pm**

I understood what you were trying to do - but the script I posted works correctly for me. Perhaps you could post the actual stack online somewhere, and we can determine why it's not behaving as expected.

Jan Schenkel.

Posted: **Mon Jun 22, 2009 8:40 pm**

Hi Jan,

you can see the stack here:

http://files.me.com/umth

Thanks

Posted: **Mon Jun 22, 2009 10:05 pm**

Found the problem: there is another group named "StoreyValueGrid" on your card, and it's not a data grid (it's just a few pixels high and wide, near the top of the card)
Use the Inspector palette or the Application browser to find and delete that group, and the script will work.

HTH,

Jan Schenkel.

Posted: **Mon Jun 22, 2009 10:37 pm**

Very thanks Jan and

sorry if I made you lose time

Posted: **Tue Jun 23, 2009 6:00 am**

No problem at all - happens to newbies and veterans alike - I'm sure you'll check the Application browser religiously from now, when something doesn't work as expected. It's the way we learn: by making lots of mistakes and hopefully drawing the right conclusions from those errors

Jan Schenkel.

Posted: **Thu Aug 13, 2009 1:05 pm**

Hi,

I'm finishing my stack, and I have a problem:

if I put a number in the field, Storey, and that is not listed in the column of

datagrid Storey StoreyValueGrid, the search produces no results (for

example if I enter the number 26).

The solution would be to add the rows with numbers and values, but

should I create a datagrid huge!

How should I do?

For now, to run the stack, I wrote the following code:

Code: Select all

on mouseUp
    if fld "Storey" <= 15 then
      put 0.43 into fld "ValueStorey"
   else
 if fld "Storey" <= 16 then
      put 0.43 into fld "ValueStorey"
   else
       if fld "Storey" <= 17 then
      put 0.42 into fld "ValueStorey"
   else
       if fld "Storey" <= 18 then
      put 0.42 into fld "ValueStorey"
   else
       if fld "Storey" <= 19 then
      put 0.41 into fld "ValueStorey"
   else
       if fld "Storey" <= 20 then
      put 0.41 into fld "ValueStorey"
   else
           if fld "Storey" <= 21 then
      put 0.4 into fld "ValueStorey"
   else
           if fld "Storey" <= 22 then
      put 0.4 into fld "ValueStorey"
   else
           if fld "Storey" <= 23 then
      put 0.39 into fld "ValueStorey"
   else
           if fld "Storey" <= 24 then
      put 0.39 into fld "ValueStorey"
   else
       if fld "Storey" <= 25 then
      put 0.38 into fld "ValueStorey"
   else
       if fld "Storey" <= 30 then
      put 0.36 into fld "ValueStorey"
   else
       if fld "Storey" <= 35 then
      put 0.35 into fld "ValueStorey"
   else
       if fld "Storey" <= 40 then
      put 0.34 into fld "ValueStorey"
   else
       if fld "Storey" <= 45 then
      put 0.33 into fld "ValueStorey"
   else
       if fld "Storey" <= 50 then
      put 0.32 into fld "ValueStorey"
   else
       if fld "Storey" <= 60 then
      put 0.31 into fld "ValueStorey"
   else
       if fld "Storey" <= 70 then
      put 0.3 into fld "ValueStorey"
   else
       if fld "Storey" <= 90 then
      put 0.29 into fld "ValueStorey"
   else
       if fld "Storey" <= 100 then
      put 0.28 into fld "ValueStorey"
   else
       if fld "Storey" <>125 then
          put 0.27 into fld "ValueStorey"
       end if
    end if
       end if
    end if
       end if
    end if
       end if
    end if
       end if
    end if
       end if
    end if
 end if
 end if
       end if
    end if
       end if
    end if
       end if
    end if
    end if
end mouseUp

Posted: **Thu Aug 13, 2009 1:21 pm**

Buongiorno Daross,

I have no idae about your actual problem, but can give you a hint how you can make you last script a bit more readable (readable at all

):

Code: Select all

on mouseup
     put fld "Storey" into tStorey

     switch 
     case tStorey <= 16 
     ## This also includee <= 15!
         put "0.43" into fld "ValueStorey"
     break

     case tStorey <= 18 
        put "0.42" into fld "ValueStorey"
     break

     case tStorey <= 20 
        put 0.41 into fld "ValueStorey"
     break

     case tStorey <= 22
        put "0.4" into fld "ValueStorey"
      break
      ### etc.
      ### You get the picture :)

   end switch
end mouseup

Best

Klaus

Posted: **Thu Aug 13, 2009 1:35 pm**

Hi Klaus,

I should edit the script of Jan:

Janschenkel wrote:You can do this using the 'lineOffset' function, and the knowledge that columns are separated by tabs. Assuming that you're using a DataGrid object:
Code: Select all
on mouseUp 
   put the dgText of group "StoreyValueGrid" into tText
   put lineOffset(field "Storey" & tab, tText) into tLineNumber
   if tLineNumber > 0 then
      set the itemDelimiter to tab
      put item 2 of line tLineNumber of tText into field "ValueStorey"
   else
      answer "Could not find it"
   end if
end mouseUp
By appending the tab delimiter to the first parameter of the lineOffset function, we ensure that we get exactly that line, and not another line that happoens to start with the same characters (think: 'foo' and 'foobar' start with the same 3 characters, but you really want 'foo' and not 'foobar' when you look for 'foo')

HTH,

Jan Schenkel.

Here you can see, the stackhttp://picasaweb.google.com/pannellirad ... 01103154

Posted: **Fri Aug 14, 2009 3:11 pm**

no answer? Sorry, I explained bad
In my stack I make:
one button
two fields "Storey" "ValueStorey"
one datagrid "DataValueStorey" (is a mini-database) composed so:

Storey Value
15 0.43
16 0.43
17 0.42
18 0.42
19 0.41
20 0.41
21 0.4
22 0.4
23 0.39
24 0.39
25 0.38
30 0.36
35 0.35
40 Â Â 0.34
etc.

The script is:

Code: Select all

on mouseUp 
   put the dgText of group "StoreyValueGrid" into tText 
   put lineOffset(field "Storey" & tab, tText) into tLineNumber 
   if tLineNumber > 0 then 
      set the itemDelimiter to tab 
      put item 2 of line tLineNumber of tText into field "ValueStorey" 
   else 
      answer "Could not find it" 
   end if 
end mouseUp

The problem:
if I put a number in the field "Storey", and that is not listed in the column "Storey" of datagrid "StoreyValueGrid", the search produces no results
For example if I enter the number 26 ( is not listed in the column) the script should be put in the field "ValueStorey" 0.36.
How can I do?
Thanks in advance

Who wants to download the stack is here
http://files.me.com/davide.rossato/0048ls

Posted: **Fri Aug 14, 2009 5:20 pm**

Buongiorno Daross,

well, obviously you cannot use "lineoffset" in that case.
I would use the "enhanced" version of your initial script

Best

Klaus

Posted: **Fri Aug 14, 2009 6:57 pm**

Davide,

try

Code: Select all

on mouseUp
    put field "Storey" into tSearch

    -- lets do a little errorchecking
    if tSearch is empty then exit mouseUp
    
    -- check if it is a number at all
    if tSearch is not a number then
        answer "Storey must be a number"
        exit mouseUp
    end if
    
    
    put the dgText of group "StoreyValueGrid" into tText
    
    set the itemdelimiter to tab
    
    -- check if number is smaller then smallest Storey
    if tSearch < item 1 of of first line of tText then
        -- do what is appropriate
        answer "Storey is too small"
        exit mouseUp
    end if
    
    -- check if number is higher then highest in datagrid
    if tSearch >  item 1 of last line of tText then 
        put item 2 of last line of tText into field "ValueStorey"
        exit mouseUp
    end if
    
    
    
    put 0 into tCounter
    repeat for each line aLine in tText
        add 1 to tCounter
        if tSearch = item 1 of aLine  then
            put item 2 of aLine into field "ValueStorey"
            exit  repeat
        end if
        if  tSearch < item 1 of aLine then 
            put item 2 of line tCounter -1 of tText into field "ValueStorey"
            exit repeat
        end if
    end repeat
end mouseUp

in your button. It works for me, but I did only limited testing.
regards
Bernd

Posted: **Sun Aug 16, 2009 1:56 pm**

Klaus, thank you, now I use the structure if then else most cleanly

Bernd, wow! your script works perfectly!

Danke!

A question; what is aLine?

LiveCode Forums.

Function VLOOKUP in Excel: which is the equivalent in REV?

Function VLOOKUP in Excel: which is the equivalent in REV?