EXEC_SP

[RocketRod]

This is just a note to explain how you might use the new EXEC_SP option to make powerful processes in AwareIM and hopefully give others some ideas. I’ve explained the processes rather than show code to demonstrate the power of this new feature.
I have built a CMS BS which has a LARGEBO that needs to be updated regularly for a large number of instances by a group of my users. I had been up till now using In Line Editing via a query but it is still rather slow and of course you have to move between pages etc. So I have developed a two stage simple process for users to EXPORT their part of the LARGEBO to Excel and then IMPORT it back with their changes. Yes there are ways to do it with other existing functions of AWAREIM incorporating the IMPORT function but the EXEC_SP option simplifies it, makes it more powerful and runs quicker. The two processes (menu options) do the following:

EXPORT MY DATA
1. Allows the user to select how they want the Excel data sorted so that it is pre sorted for them. (PICK ONE instance of a BO).
2. Call the EXEC_SP function passing the LoggedIn username and the sort type as parameters.
3. It first clears the previous external existing EXPORTBO instances for the user using the username parameter passed.
4. The EXEC_SP option then copies the users LARGEBO instances to EXPORTBO and fills three attributes called sort1, sort2 and sort3 with the required sort data.
5. Return to AwareIm process and Display query of the EXPORTBO sorted by the three attributes.
6. User uses the standard export to excel feature of the query to export their information to a file and folder selected by them.

They then work offline using Excel to change the large amounts of data and can use such features as drop and drag to copy cells etc. They can also add new rows with a blank ID number which maybe copied from another row!

IMPORT MY DATA
1. Have the user upload their file into a document attribute of regular user BO.
2. Export the document to a file incorporating in the filename the LoggedIn username.
3. Call the EXEC_SP function passing the LoggedIn username and the filename.
4. The EXEC_SP procedure now does the following:

- Uploads the Excel file into an existing external BO called IMPORTBO.
- Compares the IMPORTBO and EXPORTBO tables and flags those instances that have been changed.
- For those flagged as changed update the instances of the LARGEBO.
- For those flagged as changed insert a copy of both the IMPORTBO and EXPORTBO into an extsting external AUDITBO, setting an audit type attribute in the AUDITBO to say WAS and IS.
- Flag those instances in the IMPORTBO that are new and copy them to AUDITBO with an audit type of NEW.
5. Return to AwareIM process and use the Create function to add new instances to LARGEBO from those flagged as new on the IMPORTBO. I always add instances within AwareIM.
6. Display a message to the screen asking the user to check their changes.
7. Display query of the AUDITBO for the LoggedIn user which shows them for each instance changed what it was and what it is now! Plus new instances added.

Both procedures run very quickly almost instantaneous as they are working at the SQL DB level. My users are much happier being able to work offline and use the arrow keys of Excel to move about the large volume of data. Plus I have an AUDITBO which keeps a track of all changes which are of course timestamped showing both old and new instances.


Cheers Rod

[aware_support]

Thank you very much for this write-up!

[ninap]

hello.. this is a very useful post. we wanted to ask if you have any examples of the EXEC_SP to export/import a BO's attributes ?

we do a lot of testing and also create BO's with >100 attributes and would like to simply use the excel .csv we have to create all attributes after editing.

thanks, nina