Alpha Video Training
Results 1 to 11 of 11

Thread: LOOKUP within a calculated field expression

  1. #1
    Member
    Real Name
    Matthew Davison
    Join Date
    Jun 2010
    Location
    Buffalo, NY
    Posts
    36

    Default LOOKUP within a calculated field expression

    When I write this expression for a calculated field the expression builder tells me that the following expression is invalid:

    Amt_used * LOOKUP("components", "Prim_Comp=components->Comp_Num", "Cost_per_unit")

    "Amt_used" and "Prim_comp" are valid field names in a table named "material_usage". When I simply change the field name of "Prim_Comp" to "Comp_Num" and make the expression:

    Amt_used * LOOKUP("components", "Comp_Num=components->Comp_Num", "Cost_per_unit")

    It evaluates perfectly. Do the field names in separate tables need to match for the lookup to work properly?

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: LOOKUP within a calculated field expression

    Amt_used * LOOKUP("components", "Comp_Num=components->Comp_Num", "Cost_per_unit")
    This may evaluate correctly but does it give you the desired result? Unless I'm missing something the filter expression in your second parameter matches every record in the lookup table.

    Take a minute and tell us what you're doing.

    Take a second minute and tell where you're defining the calc expression.

    Take a third minute and tell us about the table and or set structures that are present.

    Thanks.

    -- tom

    ps. Welcome to the message board!

  3. #3
    Member
    Real Name
    Matthew Davison
    Join Date
    Jun 2010
    Location
    Buffalo, NY
    Posts
    36

    Default Re: LOOKUP within a calculated field expression

    With this expression I have "Amt_Used" which refers to the amount of "Prim_Comp", the primary component used in an operation. These two fields are located in the table "material_usage.dbf" and the expression is being written for a calculated field expression in material_usage named "comp_cost".

    "Comp_Num" refers to a field in the table "components.dbf" that contains unique codes for different operation components. "Cost_per_unit" is a field in this table as well and contains the cost per standard unit of this compound.

    What I'm trying to do is calculate how much it cost to use a certain amount of primary component for an operation.

  4. #4
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,201

    Default Re: LOOKUP within a calculated field expression

    Quote Originally Posted by Sperrrow View Post
    With this expression I have "Amt_Used" which refers to the amount of "Prim_Comp", the primary component used in an operation. These two fields are located in the table "material_usage.dbf" and the expression is being written for a calculated field expression in material_usage named "comp_cost".

    "Comp_Num" refers to a field in the table "components.dbf" that contains unique codes for different operation components. "Cost_per_unit" is a field in this table as well and contains the cost per standard unit of this compound.

    What I'm trying to do is calculate how much it cost to use a certain amount of primary component for an operation.
    Amt_used * LOOKUP("components", "Comp_Num=components->Comp_Num", "Cost_per_unit")
    How is the expression based on the Materials_usage table capturing a field value based in the Components table? I think you didn't take that third minute that Tom suggested....
    Take a third minute and tell us about the table and or set structures that are present.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  5. #5
    Member
    Real Name
    Matthew Davison
    Join Date
    Jun 2010
    Location
    Buffalo, NY
    Posts
    36

    Default Re: LOOKUP within a calculated field expression

    Gotcha,

    Well the tables I'm using here are:

    material_usage(Mt_Number, Op_Code, Prim_Comp, Amt_Used, Op_Comp_1, Op_Comp_2, Prcnt_Scrap, Comp_Cost, Scrap_Cost)

    and

    components(Comp_Num, Component, Supplier, Cost_Per_Unit, Supp_Desc, Mt_Desc)

    I have a set "component_fk" linking material_usage as a child table to the parent table "components" where material_usage(Prim_Comp), material_usage(Op_Comp_1) and material_usage(Op_Comp_2) refer to a value in components(Comp_Num)

    Op_Comp_1 and Op_Comp_2 are non-required character fields for primary component substitutions. The fields that I'm trying to compare are of the same type and length as well.
    Last edited by Sperrrow; 06-23-2010 at 11:33 AM.

  6. #6
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: LOOKUP within a calculated field expression

    What are the field types of

    material_usage->Prim_Comp

    and

    Components->Comp_Num
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  7. #7
    Member
    Real Name
    Matthew Davison
    Join Date
    Jun 2010
    Location
    Buffalo, NY
    Posts
    36

    Default Re: LOOKUP within a calculated field expression

    material_usage->Prim_Comp and components(Comp_Num) are both character type fields with length 16

  8. #8
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: LOOKUP within a calculated field expression

    Amt_used * LOOKUP("components", "Comp_Num='" + Prim_Comp + "'", "Cost_per_unit")


    Amt_used * LOOKUP("table_name", "field_in_lookup_table='" + field in current table + "'", "result_field_in_lookup_table")

    or

    Amt_used * LOOKUP("components", "Comp_Num= " + quote(Prim_Comp), "Cost_per_unit")
    Last edited by Tim Kiebert; 06-23-2010 at 11:43 AM.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  9. #9
    Member
    Real Name
    Matthew Davison
    Join Date
    Jun 2010
    Location
    Buffalo, NY
    Posts
    36

    Default Re: LOOKUP within a calculated field expression

    You make this stuff look easy ^_^

    Thanks for all the help guys!

    I scoured the online help about building a filter expression and couldn't find anything even close to this form.

  10. #10
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default Re: LOOKUP within a calculated field expression

    Your welcome

    Take a fourth minute , ;) and figure out why it works.

    Here is a link from the help

    and another from a serial poster ;)
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  11. #11
    Member
    Real Name
    Matthew Davison
    Join Date
    Jun 2010
    Location
    Buffalo, NY
    Posts
    36

    Default Re: LOOKUP within a calculated field expression

    I had actually looked at both of those resources. The actual help page was confusing because I don't think I was able to define query.filter in a calculated field expression although the form is the same when doing that. I did however use that to aid in the referential integrity issues I had because this is an entirely web-based project. I had been referring to http://www.alphafivewiki.com/Expression+Components for most of the time while trying to figure out this calculation issue.

Similar Threads

  1. Calculated field expression
    By steinmanal in forum Alpha Five Version 7
    Replies: 1
    Last Post: 01-29-2007, 02:19 PM
  2. calculated field expression
    By EDDYSOL37 in forum Alpha Five Version 7
    Replies: 9
    Last Post: 08-01-2006, 01:51 PM
  3. Calculated field expression
    By Lu Peterson in forum Alpha Five Version 5
    Replies: 9
    Last Post: 11-18-2003, 07:22 AM
  4. Need help with calculated field expression
    By Charlain in forum Alpha Five Version 5
    Replies: 4
    Last Post: 12-02-2002, 06:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •