Solved

How to display more fields: extend table or move to view or ?

  • 16 January 2023
  • 7 replies
  • 102 views

Userlevel 2
Badge +5

hi,

We have a table that needs some (say 10+) extra fields to display in a list, mostly calculated fields, some fields from a related detail (like address, pc and city from the same record).

I could make this with calculation type expression and get this data, but is this gonna give performance problems? And I need to query specific tables more than twice (address, pc, city)

So another option is to switch the table into a view, and get all the wanted data in a complex view. But there I see far more problems recreating the references to other tables and all tasks referring to data in this table as it is an important source table in the solution.

There must be something I overlooked or don’t know about in Thinkwise, how can I solve this properly? Any thoughts?

icon

Best answer by Mark Jongeling 20 January 2023, 23:47

View original

This topic has been closed for comments

7 replies

Userlevel 7
Badge +23

Hi Tejo,

If you would like to how 10 more fields in the Grid you can indeed use Expression columns, but this could lead to diminished performance as for each row in your grid, this data has to be obtained.

Creating a View for this will be better suited in terms of performance. In the View query, you can join tables more efficiently and show the data needed for this screen.

You can use the Copy table task to make an exact copy of the object including references (from 2023.1). Thereafter you would only need to change the Table type to View and use Creation method Template to start writing the View code.

Hope this helps!

Userlevel 2
Badge +5

Thanks Mark! No 2023.1 here, so I need to copy the references by hand 😊 

Copying the table also copies the context and layout control procedures, why is this? The new view is now linked to the original table’s control procedures (as is the original table) and gives errors when generating..

 

 

Userlevel 7
Badge +23

The copying of Functionality assignments was most likely implemented to ensure that a copy of a table also behalves the same thus using the same logic. We did make sure you can not copy this when copying objects from 2023.1.

You can look up the control procedures and remove the assignments for the copy.

Userlevel 2
Badge +5

ok, I see in the copied subject, there are also some fields not copied e.g. the custom-lookup and the display column. So it’s not a flawless process :-) but I think all procedures and processflows are changed now for the view.

Userlevel 2
Badge +5

hi Mark,

When joining other tables in the view, the form is not updatable. Error “View or function 'shipment_view' is not updatable because the modification affects multiple base tables.”

how can I handle this? With instead of triggers?

Do I really have to address every field (+/- 80) in the statements? When I read your first comment, I thought it was a simple change :-/ 

Userlevel 7
Badge +23

ok, I see in the copied subject, there are also some fields not copied e.g. the custom-lookup and the display column. So it’s not a flawless process 🙂 but I think all procedures and processflows are changed now for the view.

I will take a look at this when I return from my holiday 😜

hi Mark,

When joining other tables in the view, the form is not updatable. Error “View or function 'shipment_view' is not updatable because the modification affects multiple base tables.”

how can I handle this? With instead of triggers?

Do I really have to address every field (+/- 80) in the statements? When I read your first comment, I thought it was a simple change :-/ 

Instead-of trigger or Handler are the only way of having the View editable. Having 80+ fields is indeed a bit much work but you can mostly make this easy by utilizing Program object item parameters. 

Using a SQL-typed Control procedure for your Instead-of trigger or handler, you can easily add a parameter like [COL] and replace this with the col_id's from your View using the information in the Model.

Some information about it here: https://docs.thinkwisesoftware.com/docs/sf/functionality#parameters

Hope this helps making it slightly easier to maintain 😃

Userlevel 2
Badge +5

hi Mark, thanks, I’m gonna look into that. First enjoy the weekend and your holidays, cheers!