Have grid edit Dropdown menus show only certain items

Question

I am stumped and need a little push in the right direction.I have a Prices table that is comprised of several different Items.Each Item is either a Primary, Secondary or both Primary &amp; Secondary.The Options field (where primary &amp; secondary is indicated) is a Multi-Select Text field.

Within the Prices table, I have two reports:

Primary Items

Lists all items that are categorized as Primary

Secondary Item

Lists all items that are categorized as Secondary

I have a Costs table which is a join table between Prices and Jobs.The Costs table was generated prior to the Primary &amp; Secondary Items reports being created in the Prices table..The Costs table is a Grid Edit in the Jobs form.

Within the Costs grid edit, I would like the "Price - Item" list (dropdown menu) to only list items from the Secondary Items report.I thought I had it properly set up in the filters, but it isn't working.

Any thoughts?------------------------------Andrew------------------------------

andrewfry · Answer

Ok, so after re-doing the relationships to ensure I had it done correctly, and re-doing the reports, I came to remember that the filter in the join table grid edit is to SHOW Secondary Items already input, not show only Secondary Items as options. I knew that but didn't remember.

I think that I might have to generate two different Prices tables: Primary Prices, and Secondary Prices, then build my join table off of the Secondary Prices table.

I don't know why, but something rubs me the wrong way about doing it like that. It doesn't seem like the best way to accomplish the end goal.

There has got to be a way to have only the Prices table, which lists the Primary and Secondary items, then have the Join Table (Costs) only list the Secondary Items from the Prices table.

But it currently escapes me.

------------------------------
Andrew Fry
------------------------------

evanmartinez · Answer

Hi Andrew,I just want to make sure I'm understanding what you want to accomplish. You are working in a Job table and you have an embedded report link where you can add new Costs to a Job. Every Cost record is pulling down information from a parent table of Prices.&nbsp;On Prices you have a variety of Prices and some are marked as Primary and some as Secondary. You have built a report that is filtered to show only your Prices where the Type includes Secondary and when you open up that report on Prices you see only the records you want to see on your Job table in that embedded report.The issue is that on the Form in your Costs table you have it set up so when you look for a related price you can see only those secondary prices, but when you are looking at the embedded report you don't see the filtered list you see the whole list?&nbsp;If that is the case then what is happening is the report you applied to that Form works only on the Form and things like Grid Edit ignore the form. Grid Edit is a bit of a rule breaker since it happens outside of forms and their restrictions by default. The good news is that can be adjusted by overriding a report with a form. So to do that you would want to go into your Cost table settings, go into the Forms settings and "Override role settings by report"This option is only available if you have more then one form so you might just need to make a dummy copy form if you don't see the option on your forms table. Then you can find your report that you are using in your embedded report and override that report to use the Form with the right related Prices coming down by updating the Grid Edit for that report. It is a little tricky but it makes your Grid Edit look at the behavior of the Form you like. This can also change the fields on the grid edit to match the form in how they are ordered and what shows up. If your form is way different from your report you may just need to set up a form you use only for that override with all the right fields and the right reports set to your related fields. You can see where that option should appear in the image below. I hope that suggestion is helpful.&nbsp;
------------------------------Evan MartinezCommunity Marketing ManagerQuick Base------------------------------

andrewfry · Answer

Hey there Evan. Thank you so much for the response. I have been meddling with this issue for a while here now and keep coming up dry. It is relieving to have a second pair of eyes and a second brain jump in to assist. Thank you!

To answer your questions:

"I just want to make sure I'm understanding what you want to accomplish. You are working in a Job table and you have an embedded report link where you can add new Costs to a Job. Every Cost record is pulling down information from a parent table of Prices. "

Yes, that is correct. I have a Jobs table with an embedded report (grid edit) coming from the Costs table. The Costs table is pulling down information from the Prices table.

"On Prices you have a variety of Prices and some are marked as Primary and some as Secondary. You have built a report that is filtered to show only your Prices where the Type includes Secondary and when you open up that report on Prices you see only the records you want to see on your Job table in that embedded report."

Yes and no.

Yes, in the Prices table I have several different items, some marked Primary, some Secondary, and some both.
Yes, I have a report built off of the Prices table to filter for only items listed as Secondary.
No, the Secondary Items report (for lack of a defining name) is not currently linked in any way to the Jobs table. I made it thinking that I would be able to link it to the Costs report to have only the Secondary items be selectable in the dropdown menu.

"The issue is that on the Form in your Costs table you have it set up so when you look for a related price you can see only those secondary prices, but when you are looking at the embedded report you don't see the filtered list you see the whole list? "

Kind of.
Yes, when I go to select an Item from the dropdown menu in the embedded Costs report from within the Jobs form, it shows me all items, Primary and Secondary. I ned it to provide a list of only secondary items.

To give a bit of a breakdown:

A job can have a single Primary Item but multiple Secondary Items.
So I created a relationship between the Prices Table (all Items, associated prices, and type (primary, secondary) are stored here), and the Jobs table.
This allows me to select in the Job form which primary item the job is related to and the associated price. Works great.

But, because a job can have multiple secondary items, I need a join table between the Jobs and Prices, hence the Costs table.
I need to be able to add multiple secondary items to a job in a single go, rather than add one, save, add another, save, etc.
Hence the implementation of the embedded Costs report in the Jobs form.

When I go to add a new secondary item (basically creating a new cost record) from the dropdown menu in the embedded Cost report in the Job form, I need that list of items to only show Items from the price table with a type of Secondary. Hopefully that makes sense.

I am not sure if the solution you recommended is what I need because after reading your post then re-reading mine, I think that I might have not provided a clear explanation of what I need in my original post. I apologize for that.

I think I unknowingly and accidently filtered the actual Costs table (not the embedded report) to show records that only have a secondary type and then posted that in my original post, which would have caused some confusion.

But I am not ruling out your suggestion, as it might actually be what I am looking for, but not 100% sure yet.

Hopefully all that makes sense. Thanks again for your input, it helps!!

Hopefully this can be resolved soon.

P.S. I do have a way to resolve this issue, but it isn't pretty and would be inefficient.

Basically I create 2 Prices table. A Primary Prices and a Secondary Prices.

Then the join Table, Costs, is related to Jobs and Secondary Prices.

The issue with this is then I have to update two Prices tables if things change. Not pretty.

------------------------------
Andrew Fry
------------------------------

evanmartinez · Answer

Hi AndrewWhat I am suggesting you need for this solution is:Prices Table-A report showing only your secondary times (Secondary Items Report)Costs Table-A form where you have set up the reference field from the Prices table to show only items from the report (The Secondary Items Report) via the form elementsThe report you are having displayed in your embedded report on the Jobs table (Embedded for Costs)An override to the Embedded for Costs report on the Grid Edit behavior to override with the Form mentioned above*The Override steps are what I mentioned aboveJobs Table-A Grid Editable report coming from the Costs table (Embedded for Costs)What this allows you to do is say whenever I am picking a related price for my costs to pull from override the default everything with my special report. The problem is when you do this on a form grid edit ignores you. That middle step is necessary of tying your report you are using to display on the Jobs table directly to the Form you have made in the Costs table that is set up to look at the right report. Without that middle step it will ignore the limitations. That will save you the unnecessary headache of all the extra tables and manual updating. This way the report on your Prices table flows down to your Costs table and then is stamped onto your Embedded for Costs report on your Jobs table.&nbsp;It is not the most intuitive set up but it can be done, if you are having trouble getting it into place or you run into an issue I would suggest putting in a ticket to our Care team. Connecting with them is even easier now since you can click support where it appears in the top right of the Community to get routed to making a Care case. They can walk through it with you live in a screen share and help walk you through the steps, and they know where to find me if they want a second pair of eyes on it. This method most often works when you want to have a join table like you have filter your data out unless there are some complications in the middle.------------------------------Evan MartinezCommunity Marketing ManagerQuick Base------------------------------

andrewfry · Answer

Evan,Thanks for the very detailed explanation, I appreciate it.So I figured out what the issue was. I thought I had the Costs table filtered in the correct location, but I didn't. I opened a care case and the person easily corrected my mistake. I was so close but missed one thing.Again, thanks for the time to read my posts, provide great details and suggestions. I appreciate it!!------------------------------Andrew Fry------------------------------

Forum Discussion

Have grid edit Dropdown menus show only certain items

Related Content

Embedded Grid Edit Report Pre-filtered Conditional Dropdown

Form Edit/Design Menu

javascript prompt with a dropdown menu

Filter dropdown selection

Dropdown Menu Selection Appearance