Expand all | Collapse all

Conditional dropdown in embedded report

  • 1.  Conditional dropdown in embedded report

    Posted 01-02-2020 01:46
    Edited by Andrew Fry 01-02-2020 01:48
    I hope I can explain this clear enough. It seems somewhat complicated to explain due to all of the relationships and different reports; but I will give it a try ....

    I have 3 tables:
    • Jobs
    • Prices
    • Costs
    The relationships are as follows:
    • Jobs < Costs
    • Prices < Jobs
    • Prices < Costs
    The records in the Prices table are one of 3 different "Types"
    • Primary
    • Secondary
    • Primary & Secondary

    I have reports  built in the Prices table, one for Primary Type and one for Secondary Type.
    If a record has a type of Primary & Secondary, then it would populate into both the Primary Type report and the Secondary Type report.

    The Costs form is built so that the "Items" dropdown (coming from the Prices relationship) is based off of the Secondary Type report from the Prices table. Thus when I click the dropdown, it only provides me with a list of Items from the Prices table, categorized with a type of Secondary. It works as it should.

    I previously had the Costs table's "Embedded for Jobs" report embedded in the Jobs form. It was editable in Grid form from within the Jobs form. When I would select the Item dropdown in that grid edit Costs table embedded form, it would only show me the items with a type of Secondary. This was the result I was looking for and it worked as expected.

    However, I needed to include a number of additional fields in the embedded report and rather than update the "Embedded for Jobs" report, I created a new one called "Costs - Job Form - Add/Edit". I included all the fields I needed and embedded it into the Jobs form, taking the place of the "Embedded for Jobs" report.

    In that process, I accidently deleted the "Embedded for Jobs" report from the Costs table.

    Yet now, when I am in the Jobs form, and select the dropdown menu from within the embedded costs table (Costs - Job Form - Add/Edit), it shows me all items, regardless of the type. It shows me primary and secondary. It is supposed to only show me secondary.

    I have gone back and checked my costs table form. The Items menu is still linked to the Prices table and only showing the Secondary items, like it should.

    I don't know what I have done incorrectly and can't seem to figure this one out .....

    I am betting that it is something pretty simple, and I would probably catch it if it wasn't close to 1am currently.

    Any thoughts, ideas, suggestions, to help make it so that the Items menu from the embedded costs report in the job form, only shows the Secondary types from the Prices table????

    Thank you in advance and thanks for taking the time to read my light novel above ..... 


  • 2.  RE: Conditional dropdown in embedded report

    Posted 01-02-2020 07:14

    Your novel is hard to follow.  Is this the architecture you are trying to describe?  :

    Is Related Price Type is a Look Up field in the Costs table where it is inherited from the Related Price?
    I am assuming that you have three records in the Price Types Table

    1  Primary
    2 Secondary
    3 Primary and Secondary

    If so then you create a report in the Costs table, Embedded for Jobs with a Filter

    Related Price LU =2
    Related Price LU =3

    That report will only show Costs where the Parent price record has a Related Price Type = 2 or 3.

    If your architecture is different, this does not help.

    Don Larson
    Westlake OH

  • 3.  RE: Conditional dropdown in embedded report

    Posted 01-02-2020 12:25

    Thanks for the response and sorry for the lengthy novel. That is one of my NEw YEar goals: don't write a novel when explaining something!!

    I think I figured out the issue and a resolution.

    I had to create a copy of the Costs form, and override the role settings by report.

    Still working on a few of the details and nuances,, but it seems to be working correctly now.