Excel Lookup In Configurator 8.0

> I dont know know what to enclose in quotes and have been trying
> several different things, and i think the error message has just been
> leading me down the wrong path.

Just another point of syntax, there should be NO break in your string in the
file names:

run ud\test\filelookup.p ("\\mgsserver4
\epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
Output outputGENCONSUM)

Should be:

run ud\test\filelookup.p
("\\mgsserver4\epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
Output outputGENCONSUM)

i.e., you cannot break a string up onto two separate lines.

Mark W.
We are trying to use the excel lookup function in the product
configurator. We are using it on the "on leave" of a Fill-in field.
Here is the code we are using P01_CHAR_GENCONSUM = (ExcelLookup
("\\mgsserver4\epicor\mfgsys80
\Server\ud\GenTest.xls", "Sheet1", "GallonsHr", P01_CHAR_GENTYPE)).
Vantage is telling us that our syntax is correct, however, our values
are not being returned when we test inputs. We want to Lookup a value
based on the field P01_CHAR_GENTYPE and return the corresponding value
to the field P01_CHAR_GENCONSUM.

Any help would be appreciated.

Thanks,
Brad
Brad,



I have only had luck using .csv files and the following syntax on my on
leave:



run
ud\test\filelookup.p("\\ent-db22\Epicor\PCLook\sheetstock2.csv","partnum
",Stock1,output Stock1PN).



Epicor also recommends NOT going too deep in the directory structure to
find your look up file.



Todd H.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of brad.ebright
Sent: Wednesday, June 25, 2008 5:10 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Excel Lookup In Configurator 8.0



We are trying to use the excel lookup function in the product
configurator. We are using it on the "on leave" of a Fill-in field.
Here is the code we are using P01_CHAR_GENCONSUM = (ExcelLookup
("\\mgsserver4\epicor\mfgsys80
\Server\ud\GenTest.xls", "Sheet1", "GallonsHr", P01_CHAR_GENTYPE)).
Vantage is telling us that our syntax is correct, however, our values
are not being returned when we test inputs. We want to Lookup a value
based on the field P01_CHAR_GENTYPE and return the corresponding value
to the field P01_CHAR_GENCONSUM.

Any help would be appreciated.

Thanks,
Brad





[Non-text portions of this message have been removed]
> I have only had luck using .csv files and the following syntax on my on
> leave:

I'm with Todd. Do you work in Excel and save the file as a CSV and then use
the filelookup.p program. I've created other functions that read the text
files and the speed will be much faster than starting Excel for each call.

Mark.
Brad:

I also use the 'filelookup' working w/ .csv files, I'm not aware of
the 'ExcelLookup' function, on Vantage version 6.11.541.
I don't like working w/ the .csv files but the 'filelookup' works
consistently well.
>
>
> Epicor also recommends NOT going too deep in the directory
structure to
> find your look up file.
>
>
>
> Todd H.
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of brad.ebright
> Sent: Wednesday, June 25, 2008 5:10 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Excel Lookup In Configurator 8.0
>
>
>
> We are trying to use the excel lookup function in the product
> configurator. We are using it on the "on leave" of a Fill-in field.
> Here is the code we are using P01_CHAR_GENCONSUM = (ExcelLookup
> ("\\mgsserver4\epicor\mfgsys80
> \Server\ud\GenTest.xls", "Sheet1", "GallonsHr", P01_CHAR_GENTYPE)).
> Vantage is telling us that our syntax is correct, however, our
values
> are not being returned when we test inputs. We want to Lookup a
value
> based on the field P01_CHAR_GENTYPE and return the corresponding
value
> to the field P01_CHAR_GENCONSUM.
>
> Any help would be appreciated.
>
> Thanks,
> Brad
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Todd, I have tried this and again, i got syntax ok, but my value was
not brought into the configurator. Can you just tell me what the
things inside the "" mean. like ("\\ent-db22
\Epicor\PCLook\sheetstock2.csv" means the file location and so on...


--- In vantage@yahoogroups.com, "Todd Hofert" <todd@...> wrote:
>
> Brad,
>
>
>
> I have only had luck using .csv files and the following syntax on
my on
> leave:
>
>
>
> run
> ud\test\filelookup.p("\\ent-db22
\Epicor\PCLook\sheetstock2.csv","partnum
> ",Stock1,output Stock1PN).
>
>
>
> Epicor also recommends NOT going too deep in the directory
structure to
> find your look up file.
>
>
>
> Todd H.
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of brad.ebright
> Sent: Wednesday, June 25, 2008 5:10 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Excel Lookup In Configurator 8.0
>
>
>
> We are trying to use the excel lookup function in the product
> configurator. We are using it on the "on leave" of a Fill-in field.
> Here is the code we are using P01_CHAR_GENCONSUM = (ExcelLookup
> ("\\mgsserver4\epicor\mfgsys80
> \Server\ud\GenTest.xls", "Sheet1", "GallonsHr", P01_CHAR_GENTYPE)).
> Vantage is telling us that our syntax is correct, however, our
values
> are not being returned when we test inputs. We want to Lookup a
value
> based on the field P01_CHAR_GENTYPE and return the corresponding
value
> to the field P01_CHAR_GENCONSUM.
>
> Any help would be appreciated.
>
> Thanks,
> Brad
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
> ... Can you just tell me what the
> things inside the "" mean. like ("\\ent-db22
> \Epicor\PCLook\sheetstock2.csv" means the file location and so on...

For FileLookUp.P:

Param 1 - File To Use: The location and name of your CSV file
Param 2 - Column to Find: The name of the field that you want returned -
must have headers in your CSV file.
Param 3 - The value that you're looking for. FileLookup is always looking in
column 1. I've written another program that allows me to pass in the name of
the field to match.
Param 4 - The return value. Remember to include OUTPUT before this field
name when calling the program.

Mark W.
I am confused with Parameter 3 and 4. Parameter 3 would be the field
name on the input screen where the user would type the value and
would then return the rest and Parameter 4 would be the field where
the corresponding value to Paremeter 3 would go?

I'm confused as i have tried this many many times with no avail. I
always get syntax correct, but no values are returned.

Am i correct in thinking when i go to test inputs, and put my value
in it should return these numbers or do i need to test it somewhere
else?

--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> > ... Can you just tell me what the
> > things inside the "" mean. like ("\\ent-db22
> > \Epicor\PCLook\sheetstock2.csv" means the file location and so
on...
>
> For FileLookUp.P:
>
> Param 1 - File To Use: The location and name of your CSV file
> Param 2 - Column to Find: The name of the field that you want
returned -
> must have headers in your CSV file.
> Param 3 - The value that you're looking for. FileLookup is always
looking in
> column 1. I've written another program that allows me to pass in
the name of
> the field to match.
> Param 4 - The return value. Remember to include OUTPUT before this
field
> name when calling the program.
>
> Mark W.
>
> I am confused with Parameter 3 and 4. Parameter 3 would be the field
> name on the input screen where the user would type the value and
> would then return the rest and Parameter 4 would be the field where
> the corresponding value to Paremeter 3 would go?
>
> I'm confused as i have tried this many many times with no avail. I
> always get syntax correct, but no values are returned.
>
> Am i correct in thinking when i go to test inputs, and put my value
> in it should return these numbers or do i need to test it somewhere
> else?

Depends on what you're trying to do. If you're using FileLookUp.p in rules
then you would run Test Rules to check if your program is working.

Maybe if you give a sample of what you're trying to do, we can walk you
through an example for you. Maybe copy an paste your On-Leave code here as
well.

Mark W.
My excel sheet is saved as a csv and has the following columns.

Gen Number, GallonsHr, Gen Weight, Length, Width

I am trying to type in the gen number, and return the values for the
rest. Right now, i am only working on the GallonsHr column. My code
looks like this.

run ud\test\filelookup.p ("\\mgsserver4
\epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
outputGENCONSUM)

P01_ChAR_GENTYPE is the number that is in the excel sheet as Gen
Number.

outputGENCONSUM would be the GallonsHR.

Thank you for helping me through this as this is our first time
working with this. The help and patience is much appreciated.

--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> > I am confused with Parameter 3 and 4. Parameter 3 would be the
field
> > name on the input screen where the user would type the value and
> > would then return the rest and Parameter 4 would be the field
where
> > the corresponding value to Paremeter 3 would go?
> >
> > I'm confused as i have tried this many many times with no avail. I
> > always get syntax correct, but no values are returned.
> >
> > Am i correct in thinking when i go to test inputs, and put my
value
> > in it should return these numbers or do i need to test it
somewhere
> > else?
>
> Depends on what you're trying to do. If you're using FileLookUp.p
in rules
> then you would run Test Rules to check if your program is working.
>
> Maybe if you give a sample of what you're trying to do, we can walk
you
> through an example for you. Maybe copy an paste your On-Leave code
here as
> well.
>
> Mark W.
>
> I am trying to type in the gen number, and return the values for the
> rest. Right now, i am only working on the GallonsHr column. My code
> looks like this.
>
> run ud\test\filelookup.p ("\\mgsserver4
> \epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
> outputGENCONSUM)
>
> P01_ChAR_GENTYPE is the number that is in the excel sheet as Gen
> Number.
>
> outputGENCONSUM would be the GallonsHR.

Ahhh. The word OUTPUT is a separate word in the parameter list and indicates
to Progress that you're passing the parameter in by reference. So if the
field that holds the GallonsHR is called outputGENCONSUME then your call
looks like:

run ud\test\filelookup.p ("\\mgsserver4
\epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
Output outputGENCONSUM)

^^^^^
Two words in the parameter with no comma - just a space.

> Thank you for helping me through this as this is our first time
> working with this. The help and patience is much appreciated.

You'll find many very helpful folks on this list. It won't be long before
you're answering questions for others new to Epicor/Vantage.

Mark W.
THANK YOU. IT WORKED! you have made my day.

Thanks again for all the help that you gave.


--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> > I am trying to type in the gen number, and return the values for
the
> > rest. Right now, i am only working on the GallonsHr column. My
code
> > looks like this.
> >
> > run ud\test\filelookup.p ("\\mgsserver4
> > \epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
> > outputGENCONSUM)
> >
> > P01_ChAR_GENTYPE is the number that is in the excel sheet as Gen
> > Number.
> >
> > outputGENCONSUM would be the GallonsHR.
>
> Ahhh. The word OUTPUT is a separate word in the parameter list and
indicates
> to Progress that you're passing the parameter in by reference. So
if the
> field that holds the GallonsHR is called outputGENCONSUME then your
call
> looks like:
>
> run ud\test\filelookup.p ("\\mgsserver4
> \epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
> Output outputGENCONSUM)
>
> ^^^^^
> Two words in the parameter with no comma - just a space.
>
> > Thank you for helping me through this as this is our first time
> > working with this. The help and patience is much appreciated.
>
> You'll find many very helpful folks on this list. It won't be long
before
> you're answering questions for others new to Epicor/Vantage.
>
> Mark W.
>
One more thing,

If we want to fill in multiple fields from P01_CHAR_GENTYPE field,
how would we code this?

--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> > I am trying to type in the gen number, and return the values for
the
> > rest. Right now, i am only working on the GallonsHr column. My
code
> > looks like this.
> >
> > run ud\test\filelookup.p ("\\mgsserver4
> > \epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
> > outputGENCONSUM)
> >
> > P01_ChAR_GENTYPE is the number that is in the excel sheet as Gen
> > Number.
> >
> > outputGENCONSUM would be the GallonsHR.
>
> Ahhh. The word OUTPUT is a separate word in the parameter list and
indicates
> to Progress that you're passing the parameter in by reference. So
if the
> field that holds the GallonsHR is called outputGENCONSUME then your
call
> looks like:
>
> run ud\test\filelookup.p ("\\mgsserver4
> \epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
> Output outputGENCONSUM)
>
> ^^^^^
> Two words in the parameter with no comma - just a space.
>
> > Thank you for helping me through this as this is our first time
> > working with this. The help and patience is much appreciated.
>
> You'll find many very helpful folks on this list. It won't be long
before
> you're answering questions for others new to Epicor/Vantage.
>
> Mark W.
>
> If we want to fill in multiple fields from P01_CHAR_GENTYPE field,
> how would we code this?

If the file isn't too big, you'd just call the program multiple times in
your on-leave section using a different field in the OUTPUT parameter.

If performance becomes an issue, you can put all of your data into one field
with a different field separator (| or ^ or whatever). You'd make the one
call and then use the ENTRY function to parse out the fields and assign them
to each field.

I recommend downloading the Progress ABL reference (or use the HELP in the
Progress Procedure Editor) and learn about these functions when using
delimited strings: ENTRY, LOOKUP, NUM-ENTRIES.

Mark W.
I have been trying to figure out what the correct syntax would be to
call it multiple times and i'm struggling to find the correct way.

Do you have some example code that does this?

--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> > If we want to fill in multiple fields from P01_CHAR_GENTYPE field,
> > how would we code this?
>
> If the file isn't too big, you'd just call the program multiple
times in
> your on-leave section using a different field in the OUTPUT
parameter.
>
> If performance becomes an issue, you can put all of your data into
one field
> with a different field separator (| or ^ or whatever). You'd make
the one
> call and then use the ENTRY function to parse out the fields and
assign them
> to each field.
>
> I recommend downloading the Progress ABL reference (or use the HELP
in the
> Progress Procedure Editor) and learn about these functions when
using
> delimited strings: ENTRY, LOOKUP, NUM-ENTRIES.
>
> Mark W.
>
> I have been trying to figure out what the correct syntax would be to
> call it multiple times and i'm struggling to find the correct way.
>
> Do you have some example code that does this?

Just one right after another (assuming you're doing an On Leave):

run ud\test\filelookup.p ("\\mgsserver4
\epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
Output outputGENCONSUM)

run ud\test\filelookup.p ("\\mgsserver4
\epicor\configtest\GenTest.csv","Gen Weight",P01_CHAR_GENTYPE,
Output outputGENWEIGHT)

run ud\test\filelookup.p ("\\mgsserver4
\epicor\configtest\GenTest.csv","Length",P01_CHAR_GENTYPE,
Output outputLENGTH)

run ud\test\filelookup.p ("\\mgsserver4
\epicor\configtest\GenTest.csv","Width",P01_CHAR_GENTYPE,
Output outputWIDTH)

Mark W.
I tried that a got the following syntax error.

**Invalid keyword found. Enclose it in quotes: run (349)

I dont know know what to enclose in quotes and have been trying
several different things, and i think the error message has just been
leading me down the wrong path.

--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> > I have been trying to figure out what the correct syntax would be
to
> > call it multiple times and i'm struggling to find the correct way.
> >
> > Do you have some example code that does this?
>
> Just one right after another (assuming you're doing an On Leave):
>
> run ud\test\filelookup.p ("\\mgsserver4
> \epicor\configtest\GenTest.csv","GallonsHr",P01_CHAR_GENTYPE,
> Output outputGENCONSUM)
>
> run ud\test\filelookup.p ("\\mgsserver4
> \epicor\configtest\GenTest.csv","Gen Weight",P01_CHAR_GENTYPE,
> Output outputGENWEIGHT)
>
> run ud\test\filelookup.p ("\\mgsserver4
> \epicor\configtest\GenTest.csv","Length",P01_CHAR_GENTYPE,
> Output outputLENGTH)
>
> run ud\test\filelookup.p ("\\mgsserver4
> \epicor\configtest\GenTest.csv","Width",P01_CHAR_GENTYPE,
> Output outputWIDTH)
>
> Mark W.
>