Anyone of you more experienced users know if it is possible to use a button in Opreations with records on a view, but the changes will be done on the BO the view is showing records from?
I need to use a view to show the correct records for several reasons, but I also need to give the users an edit-option for each record.
For adding new records I added a button in Panel operations on the wiew query and use a process to show the form for the BO so the user adds new records to the BO. This works just fine. But for editing, I will need some magic to edit the correct record in the BO based on the record they choose to edit in the view. Is this even possible? Or do I have to make the users enter the ID of the record they want to edit or something like that as a workaround?
Many thanks for any input on this.
[SOLVED]Operations with records in View query, but do changes in BO?
[SOLVED]Operations with records in View query, but do changes in BO?
Last edited by lueu on Mon Nov 27, 2023 2:36 pm, edited 1 time in total.
Re: Operations with records in View query, but do changes in BO?
If a query's text view looks something like this: EXEC_SP "readSomeData" RETURNS myBO then the data is view only. You do not have access to the rows when the user clicks on them or in an Operation with Records.
When you need a view (or just an SP to create the columns you need displayed, you need to do the following:
1). Create a BO with all of the columns (attributes) you want to display / work with
2). Create a stored procedure that will populate the newly created BO (probably deleting all of the rows 1st. so it might look like this
DELETE FROM myNewBO
INSERT INTO myNewBO (ID, BASVERSION, BASTIMESTAMP, col1, col2 .....)
SELECT NEXT VALUE FOR BAS_IDGEN_SEQ, 1, getdate(), col1, col2 ... FROM .....WHERE....
3). Create a procedure that will call the SP then Display your query that is now showing columns from the new BO
Hope this helps
Bruce
When you need a view (or just an SP to create the columns you need displayed, you need to do the following:
1). Create a BO with all of the columns (attributes) you want to display / work with
2). Create a stored procedure that will populate the newly created BO (probably deleting all of the rows 1st. so it might look like this
DELETE FROM myNewBO
INSERT INTO myNewBO (ID, BASVERSION, BASTIMESTAMP, col1, col2 .....)
SELECT NEXT VALUE FOR BAS_IDGEN_SEQ, 1, getdate(), col1, col2 ... FROM .....WHERE....
3). Create a procedure that will call the SP then Display your query that is now showing columns from the new BO
Hope this helps
Bruce
Re: Operations with records in View query, but do changes in BO?
Thanks, Bruce!
I will try this!
I will try this!
Re: Operations with records in View query, but do changes in BO?
So far so good, but i have a follow up question:
"Create a procedure", do you mean I need to do this in a process, first step EXEC_SP, second step DISPLAY myNewBO? Or, can I make a query somewhere in the process to display instead of dispalying the BO, since I need to be able to edit which columns are shown, sorting and filtering and so on. This can't be done by diplaying a BO, if I remember correctly?
EDIT: I created a query and displayed this, and it works. I now get the same result as I did in my view
"Create a procedure", do you mean I need to do this in a process, first step EXEC_SP, second step DISPLAY myNewBO? Or, can I make a query somewhere in the process to display instead of dispalying the BO, since I need to be able to edit which columns are shown, sorting and filtering and so on. This can't be done by diplaying a BO, if I remember correctly?
EDIT: I created a query and displayed this, and it works. I now get the same result as I did in my view
Re: [SOLVED]Operations with records in View query, but do changes in BO?
I am confused. Is it working right or do you still have a problem.
Also, not sure why you need to access a view vs. an Aware BO with shortcuts to other tables.
Also, not sure why you need to access a view vs. an Aware BO with shortcuts to other tables.
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: [SOLVED]Operations with records in View query, but do changes in BO?
If the relationships have been formed properly then there is no need for SQL Views as Bruce says, every BO has a path to its relationed BOs through shortcuts or the relationship path e.g. Registration.psVehicle.psModel.psManufacturer.Name. Where you might have problems is in peer multiple relationships in which case you can fix that by choosing the object into which there is a singular path.
An example of what the OP is trying to achieve would be helpful as it will allow others to see if there is a conceptual misunderstanding versus a technical limitation that is being hit.
Re: [SOLVED]Operations with records in View query, but do changes in BO?
The problem is solved doing what Bruce suggested. I created a BO, then created a stored procedure to populate the new BO from the view. To display the result correctly I used a process with an EXEC_SP and a DISPLAY the query I made from the new BO. I also added the process to the Save-button to make sure the SP is executed when canges have been done. Works like a charm.
I use a view because I need to join several tables, icluded external ones, in order to show the correct data to the logged in user. Also the users needs to be able to sort and filter the data. This I have learned on here can not be done using a stored procedure as I first tried. Shortcuts to other tables did not work the way I needed them to, since I not only need to show an attribute in a form based on another attribute, but also needs this to be saved to the BO. Even if I can show them on my form, I could not save to the BO.
To sum up, this works, and I have marked the question as solved, since I've seen this been done on other posts.
I hope this cleared things up a bit. Thanks for all help, this forum is great when I get stuck, I get a lot of help both searching others posts and asking questions when I can't find the answer in old posts
I use a view because I need to join several tables, icluded external ones, in order to show the correct data to the logged in user. Also the users needs to be able to sort and filter the data. This I have learned on here can not be done using a stored procedure as I first tried. Shortcuts to other tables did not work the way I needed them to, since I not only need to show an attribute in a form based on another attribute, but also needs this to be saved to the BO. Even if I can show them on my form, I could not save to the BO.
To sum up, this works, and I have marked the question as solved, since I've seen this been done on other posts.
I hope this cleared things up a bit. Thanks for all help, this forum is great when I get stuck, I get a lot of help both searching others posts and asking questions when I can't find the answer in old posts