0

Query Help for a VIEW. Unexpected behavior.

I have a PLAN HEADER and PLAN DETAIL parent-child structure. I have it setup so that I use a slider when ON shows ALL PLAN DETAIL line items, and when OFF, should only show PLAN DETAIL Lines that are actually being planned. (There are more ITEMS than I might have in the PLAN.)

Data Model:  [PLAN HEADER] --> [PLAN DETAIL]<--[ITEMS]

I have am using a VIEW to present the PLAN DETAIL on the PLAN HEADER form. I did it this way so I could toggle the line item content. SEE figure A.

 

MY ISSUE:  When I am in a PLAN HEADER and the SHOW ALL toggle is on, if there are no plan lines, then it is blank (as expected). When it is SHOW ALL is OFF, I see a subset of plan lines even though it is the wrong Header ID. SEE figure B.

 

Here is the Code I am using for the view.

let x := 'Plan Header ID';
if 'Display All Items' = true then
select 'Plan Lines' where 'Plan Header' = x
else
if 'Display All Items' = false then
select 'Plan Lines' where 'Plan Header' = x and 'UN/Month' > 0 or 'Beg INV-UN' > 0  (I am checking to see if it is a planned line OR if there is a beginning inventory)
end
end

 

Ideas why it is not working?

5 replies

null
    • Chapel_of_Praise
    • 3 yrs ago
    • Reported - view
    • Chapel_of_Praise
    • 3 yrs ago
    • Reported - view
    • John_Halls
    • 3 yrs ago
    • Reported - view

    As you have a relationship there is no need to use the select statement, also there's no need for two if statements and lastly I think your boolean test uses two 'or' elements and this might be the cause of the confusion.  Your code can be simplified to

    if 'Display All Items' = true then
    'Plan Lines'
    else
    'Plan Lines'['UN/Month' > 0 or 'Beg INV-UN' > 0]

    end

     

    Regards John

    • Chapel_of_Praise
    • 3 yrs ago
    • Reported - view

    Thank you John. Syntax is still a mystery for me. I appreciate your help.

    • Chapel_of_Praise
    • 3 yrs ago
    • Reported - view

    John, does this work for an embedded child table as well? The reason I went to a view is that I could not figure out how to filter the child (composite) table view. Possible?

Content aside

  • 3 yrs agoLast active
  • 5Replies
  • 299Views