Alpha Video Training
Results 1 to 3 of 3

Thread: variable "expression" in report

  1. #1
    Member
    Real Name
    Philip Troutt
    Join Date
    Jul 2006
    Posts
    14

    Default variable "expression" in report

    I want to add a field to a report that is not in any of the report tables, but is linked to a field in one table.

    I'm used to using "IN...WHERE" syntax, but this doesn't appear to exist in v8.

    Specifically, I want to use the field "prod_category" as a Group Header. This field exists in the "products" table which is not one of the report tables. In is linked to the Trans_detail table, which is a report table, by the "productid" field.

    I've tried creating a variable at the report level, but that doesn't work. I seem to have exceeded by expertise on this one.
    Last edited by dptroutt; 12-02-2007 at 05:18 PM.

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

    Default Re: variable "expression" in report

    You can use a calculated field in the report. For the expression incorporate one of the Lookup functions.
    The Lookup() function takes a filter - more flexible
    Code:
    lookup("products","ProductId="+quote(Trans_detail->ProductId),"prod_category")
    And the LookupC() function uses an index - more quicker
    Code:
    lookupc("F",Trans_detail->ProductId,"prod_category","products","AnIndexOnProductID")
    I'll pass on some advice that I have received from this board. A lookup function will open and close the table for each and every instance that it needs to find a value. This could be a lot of overhead if you have many records. Instead add the table to the set (1 - 1)for reporting purposes. Then the table is opened once,along with the rest of the set, and you can drop the fields on directly without needing calculations.
    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.

  3. #3
    Member
    Real Name
    Philip Troutt
    Join Date
    Jul 2006
    Posts
    14

    Default Re: variable "expression" in report

    Tim,

    Thanks. I added the table to the set, as this seems to be the consensus method.

Similar Threads

  1. Replies: 0
    Last Post: 09-16-2007, 12:07 PM
  2. Expression to set a numeric null field to zero "0.00"
    By kdankwah in forum Alpha Five Version 7
    Replies: 6
    Last Post: 09-13-2007, 12:17 PM
  3. "Invalid Report Filter Expression"
    By lemow in forum Alpha Five Version 8
    Replies: 22
    Last Post: 04-17-2007, 07:32 PM
  4. Keep getting the error:"Conflicting expression data types"
    By lemow in forum Alpha Five Version 7
    Replies: 9
    Last Post: 12-06-2006, 07:36 PM
  5. "Filter expression not logical" on Table lookup.
    By Graham Wickens in forum Alpha Five Version 6
    Replies: 2
    Last Post: 12-24-2005, 04:29 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
  •