I need some quick guidance on the best way to approach my issue. I’m not sure if I should use a portal or a tabpanel to present related records when I have to filter out some of the related records.
-I’m showing a master record from a main table on a form.
-Then I need to display related records in a portal/panel from a join table below the master record.
-I also need to reach thru the relation, which only gives me a product_id, and do another join to the product table so I can display the product description.
-I only want to display related records where fieldx > 0. I need to filter out the related records if fieldx is 0.
I understand databases and relations pretty well. I’m just not familiar enough with Servoy to know the best approach to filter out the records in the portal. I’m also wondering if a portal isn’t the best way…if I should be using a tabpanel or something.
I’m also fuzzy on the best way to show the related records when I need to get the product description from a 3rd table. (join within a join)
Do I need to write custom SQL to get a dataset this complicated, or is there an easier way to do it with something simple in Servoy? Do I use a portal or something else?
Any guidance would be greatly appreciated. I’m just looking for strategy here, not all the code.
TIA!
Hi Jason,
You can do all what you want with 1 global, relations and a tabpanel.
First you create a global of type Number with a default value of 1. Lets say it’s named 'minumumVal.
Then you create the relations that go from master-child and child-product.
On the master-child relation you add the extra join setting global.minimumVal > fieldx.
Then you create the child form as list/table view and place a label on there.
In the label text you can use %%columnname%% tags.
The fun bit is that you can use multiple relations in here as well.
So you can use %%masterchild.childproduct.productname%%. But since we are in the child form already you only need to use the last relation (%%childproduct.productname%%).
Make sure the checkbox ‘displayTags’ is checked.
Now link this form to your masterform through a related tabpanel and you are set.
And because you are using a global you can set the minumum value on the fly and filter the list accordingly.
Hope this helps.
That worked beautifully! Thank you.
Next question…
This gives me a static list of records in the tabpanel that I don’t seem to be able to edit or select in any way.
I need to be able to select a row in the tabpanel list of related records and edit the price. I also need to be able to select a row and delete it.
Do I need an entirely different approach or can I extend the solution offered above?
Nevermind on that last question…
Instead of using tags (%%price%%) on the tabpanel, I put the field directly on the form. It then became editable.
In fact, I was a little surprised that if I edited the price on the form, it was actually changing the record in the database live. I was thinking I would have to initiate some sort of method to push the change.
Thanks again. This forum is quite a resource!