Grid Drop Down Box

[Dan_Foster]

I know this is simple but I can't get the syntax right. I want to filter the values in a grid drop down box.

The Drop Down Box is in the "Activities" grid on the "Activity_Contact" field. I need to filter records from the "Contacts" grid based on the "Account_id" which is present in the "Activities" grid and the "Contact" grid.

I built a filter under "Choices" for the Drop Down Box "Account_id={Account_id}" but it doesn't work.

[peteconway]

Re: Grid Drop Down Box

Are you using V10? Are you trying to contrain the second dropdown based on the previous dropdown? - send a screen shot.

[Dan_Foster]

Re: Grid Drop Down Box

I am using Ver. 5.9. This is a simple, one level Drop Down Box.

I think I have attached screen shots. We'll see.



Thank you.

[Dan_Foster]

Re: Grid Drop Down Box

Still cannot figure this out. I am simply trying to figure out how to fileter a dropdown box with only contacts for the account.

.DropDownBox.Filter = "Account_Id = Account_id"

This is what I have now but all contacts come back to the DDB.

[MikeC]

Re: Grid Drop Down Box

Have you tried the following for syntax yet?
DropDownBox.Filter = "Account_Id ="+quote( Account_id)

I also dropped the period in front of

.DropDownBox.Filter

which perhaps was just a typo.

[Dan_Foster]

Re: Grid Drop Down Box

I cut and pasted this code into the component and I got a publishing error "3". Also, the period was placed there by the Alpha 5. I tried taking it out but still got the error.

[Dan_Foster]

Re: Grid Drop Down Box

I am going crazy and it will be a short trip according to my wife.

I could sure use some help, nothing has worked so far.

I have a parent table called "tbl_account" and a child table called "tbl_contact". They are linked with a common field called "Account_id" "Account_id" is a auto-incrementing numeric field in "tbl_account" and a numeric field in "tbl_contact".

I have a grid called "pending" that is based on a table called "tbl_penidng". The "pending" grid is used to schedule and edit activities. The "tbl_pending" table is tied to the "tbl_account" with the "Account_id" field, just like "tbl_contact".

The "Contact" field in the "pending" grid is a drop down box used to select which account contact is the target of the activity.

My drop down box works but it returns every contact in the "tbl_contact" table. I want to filter the values based on the "Account_id" so I only get contacts associated with the account the activity is tied to.

When I go to choices and try to build the filter, I cannot get it to work. I tried the centax that MikeC was kind enough to give me but I get an error when I go to live preview

"500 Internal Server Error
"Script Error
Error:Invalid component format: Pending"

Sorry to be so long winded. Thank you...

[Simone]

Re: Grid Drop Down Box

Hi Dan,
I hope you are not crazy, yet.
I found a way to solve a problem that’s either similar or the same: filtering a dropdown on a child grid based on a value in a parent grid. I also tried the filter, but it just didn’t work (I think because of the way alpha five adds quotes to the expression).
Here is a solution that does work for me(I am opening the child grid from the parent grid – I hope this applies to you):

In the parent grid define a calculated field based on your account id:
lk_account_id = tbl_account->account_id.
Add it to your grid and select “link” as control type.
Choose “computed from fields in the grid”; select the linking fields, then define a variable:
“Variable name” = session.account_id and
“Contains value” {account_id}
(where account_id is the name of the field in the grid – I think it gives you a list of choices)

Then go to the page that contains your child grid and override the dropdown for your contacts field by placing the following code after the with tmpl… section (after the component has been loaded with a5w_load_component(…):

If eval.valid(session.account_id)
field_info[n].DropDownBox.Filter = “account_id=”+quote(session.account_id)
end if
(look up “n” in the xbasic code of the child component)

I hope this is helpful.
Good luck,
Simone

[Dan_Foster]

Re: Grid Drop Down Box

Thanks Simone.

I got lost when I tried to follow your instructions. My account_id field is already on my child table where the dropdownbox is located.

[Simone]

Re: Grid Drop Down Box

Hi Dan,

What I am describing in the previous post is how to:
create a parent grid (accounts). Each of its records contains a link to the child grid(activities). The link passes along the account_id as session variable. This id is then used to override the filter for the contacts dropdown in the child(activities) grid.

In more detail: add a field to the parent (account) grid that has control type “link” (additionally to the account_id field). Select “Static text” under “Display what in link?” and set the text to something that makes sense, such as “edit account activities”. When the user clicks on it (it shows up for every account record with the same label) it brings him to your child(activities) grid, the one that is based on tbl_pending, because that’s how you define it: as link address type you select “Computed from fields in the grid”. Then, when you click on the button on the link address line, you get a window that allows you to pick the page to which the user will be redirected. Pick the child page that’s based on tbl_pending and select the account_id field as linking field. Then define the variable (in the same screen) as I describe in the previous post.

So at this point, you have a parent page that displays your parent grid, the account grid. It has an account_id field which may be invisible and it has a link field that’s based on the account_id field which is visible. It displays the static text “edit account activities”. When the user clicks it, he is sent to the child page which displays data for the account_id of the record that was selected on the parent grid.

The child page knows what this account id is because it was passed along to it in the session variable.

When you define your contacts dropdown in the child grid, the data comes from the contacts table which also contains the account_id. Since setting the filter in the child grid doesn’t seem to work, you can override the filter definition for the contact dropdown in the child grid’s page. In the child component go to the xbasic tab and look for the contacts dropdown. It is referenced with a number: you should see something like: with tmpl.field_info[3] .Fieldname = "contacts" . So, remember the “3”.

Then go to the child grid’s page. It loads the component. After it has done so you can override the filter for the contacts dropdown. I think what I put in the previous post should work. So this means the contact information is taken from the contacts table, but only for the account_id that belongs to the parent grid record for which the user opened up the child (activities) page.

This is all terribly long winded. I hope it makes some sense.
Good luck,

Simone