Digital Workshop

I am attempting to duplicate an HTML-based application in Opus as a prototype. One of the pages is a Bill of Materials with 4 fields in Excel. It has 70 or more rows and re-entering these in Opus would be rather tedious and if we take this project we'll need to produce up to 70 of these apps. I set up a database action but cannot find an easy way to simply display the entire database. I've considered setting up a row of four variables but can't find a way to read in anything but the first item records.

DocView doesn't apply as I need to add rows of buttons and links to schematics, pdfs, and other information.

Anybody have any experience with this to share?

Dave

I don't quite understanf what you mean when you say ""One of the pages is a Bill of Materials with 4 fields in Excel. It has 70 or more rows and re-entering these in Opus would be rather tedious and if we take this project we'll need to produce up to 70 of these apps"
You create a DSN to point to the excel spreadsheet, do a query which will return everything, then next record.
I do this all the time and it is quite easy, which makes me suspect I am not understanding what you mean.


Perhaps the bit you aren't grasping is that when you do a query, it finds all the records which fulfil the match, but you have to tell Opus to move to the next record. It won't automatically list all the records.

Sandy

"Perhaps the bit you aren't grasping is that when you do a query, it finds all the records which fulfil the match, but you have to tell Opus to move to the next record. It won't automatically list all the records."

The help text indicates it should return all matching records.

"Next record" I haven't used as that would still only be one record. What I want is "show *.*" Actually, I am not even using this as a database. I am just looking for a way to read in the whole thing as either a table or a series of 4 very long vertical text boxes...basically just as it appears when opened in Excel.

Only operator that looked applicable is "is not null" as there is an "Item#" field that is not empty in any row.

Dave

I use a mixture of actions and scripting for my database queries.
I am not aware of how to do a null query with actions.
To retun all records, I would use a scripted SQL command, something like this:-




then a loop using NumRecords to display everything.
sorry if the exact syntax of the SQL query is not 100%, but I cut it out of a longer query.
If you look at the Opus help on scripting search for SQL, it lists all the functions available such as Next record, first record, last record, get current record number, get number of records.
Sandy

I use a mixture of actions and scripting for my database queries.
I have never tried to return all records in a query using actions. I would probably index the records starting at 1, then search for a non zero index


You could also use a scripted SQL command, something like this:-




then a loop using NumRecords as the counter to display everything.
sorry if the exact syntax of the SQL query is not 100%, but I cut it out of a longer query.
If you look at the Opus help on scripting search for SQL, it lists all the functions available such as Next record, first record, last record, get current record number, get number of records.
Sandy

First, I take it you want to accomplish this entirely in Standard Actions and not in OpusScript, correct?

Please clarify the 2nd part above: "DocView doesn't apply.... ". Are you saying that the rows of buttons and the links to external documents are separate from the display of records -- but algined to each row of records? It would be great if you could post a screenshot of an Opus Page with that mocked-up and commented a bit.

As for the "row of four variables but can't find a way to read in anything but the first item records":
I suggest you use either a common Text Object or a Listbox object for the display of your DB query results.
You can create a Page variable, eg. DBresults_All (set default as blank)
While you are at it, create two more variables: myTab and myLR (for my 'Line Return' or new-line)
Oops! [EDIT:] you should set the default value of myTab to EQ \t and the value of myLR to EQ \n

As Sandyn said, you have to tell Opus to move through the returned records one by one.
When you have 1st record, set DBresults_All = DBresults_All + field1 + myTab + field2 + myTab + field3 + myTab + field4 + myLR
** I think you can do that in standard actions by setting Variable to expression.
** You have to take a little care to make sure that the above expression will concatenate strings (and not be adding numerical values, especially with the first +value on the right side of EQ)

Then simply loop through using the same expression. [EDIT: maybe I should say drag and place the above Action inside a LOOP action which advances to each 'next record' until the end is reached.]
This will involving advancing to Next Record then doing the assignment (concatentation) of values.
You will end up with one extra blank line at the end of the records (can be prevented with a simple IF condition)

You might play with both Text object and the Listbox object to see what best meets your needs.

BTW... if you do a row of buttons and links, you can put a variable name for the Button text -- and have that variable be set to (assigned the value of) a particular field of a particular row of the DB record set. It depends what you are trying to accomplish.

ETA: When you get that working, OR as an alternate approach, you can refine the display into justified columns -- by using 4 text objects instead of one. And create for variables, one for each DB field to display. Size you Text Object width large enough for DB column contents. And 'Insert Variable' -- e.g., DBresults_Field1 --- for each field used.
Instead of concatenating all fields into a row, you simply handle each Column (field1, field2, etc) separately:
You can do the above code using Standard Actions.

Thanks to all so far. I could probably figure it out now, but as "a picture is worth a thousand words" here is what I need to replicate. As mentioned, yes, I need buttons lined up with the info from the table.

Again, no interest (at this point) in working with the db as a db...just displaying the information as shown.

As to whether it's script or standard actions, I don't really care as long as it works. Standard actions are better for my staff...but this will be the same in all 70 apps so once it's done it will be just changing the name of the db for each one.

Sorry about the image size, but there still seems to be a rather archaic limit on image size. If you can make it out, there are 4 fields per row, then three button columns for pdfs and schematics.

Dave

Okay, so that is a different framing of the problem at hand. The question is can you copy and paste your Excel-type data? If so, paste each of 4 columns of data into 4 Listbox objects.

So I see the layout with the icons. What I do not know is where you have, or will get, the link data (i.e., path and filename you want to launch). Ideally if you can copy / paste that as a column (3 columns) -- then you are on the verge of an easy solution. Hint: Fill a Listbox object with those Push-pin icons, one on each line -- matching the number of rows. Then a User clicking on an icon is specifying an index number (a row in the listbox). It is a simple matter of applying that index to do a lookup of the value in your links Listbox. Good luck.

ps. you can delete scrollbars from a Listbox.

Thanks, Lar123. I'd done that...but I'd hoped there was an easy way just to read the thing in as there will be 50-70 pubs, all identical in function, but covering different equipment. It's worth my time now to try to figure out how to automate as much of the process as I can.

Further, there will be updates. Much easier to just switch out Excel files than to have to re-compile.

We'll do what has to be done. As to the pushpins and such, haven't really decided how to handle those yet as the internal client isn't totally satisfied with the way the program works at the moment and we may change the interface somewhat.

However, I am just duplicating the existing one at the moment as a "strawman" to work from and see what is involved. It's a large project.

Regards,
Dave

BTW, the paste to a listbox thing is a no go. There are no returns after cell entry, so it pastes as one long line. Slighly better than re-entering the whole thing and probably more error prone.

Dave

Good! If you have the .XLS or whatever Excel files, you have several ways to handle the "easy read-in....".

a) you can treat an Excel file as if it were a database -- retrieving cells or rows. I think that is where you started. So you just needed to learn how to display several rows.
b) you can try to Export data from each of the 70 .xls worksheets --- if you then have 70 files with tab-delimited or comma-delimited data, you can work with that in Opus by opening and reading from the file. Displaying rows on the Opus Page is simply a matter of choice, and the discussions of Text Object and Listbox Objects above applies here as well. I prefer to use Opusscript for such tasks, but you can get there with Std Actions also. (I think the exporting step introduces possibility of more work, plus errors.)

I bet Mackavi/Interactiv can set up a working template pub for you for a reasonable fee -- if you can define it.

Thanks to all. I have a plan now and it will involve developing a routine to accomplish the formatted export of db to Opus with minimum. Perhaps such a function will be added at some point. This has been one of those things that surprised me as I'd assumed many others had needed this in the past.

Dave