Finding Specific Fields In A Database?

[RNIB]

Inspired by recent success with my 2nd ever macro I would like to rewrite my first macro as I can now see there were things that I did in that which, although worked. are not ideal.

The purpose of this macro is to copy data from one database into another application. The problem is that the database was very badly written with some fields only selectable with a mouse and the tab order is all over the place. As I work for a charity for the blind (hence the username) this means that this database isn't really accessible to blind and visually impaired users hence the need for the macro. I should also point out that the software was custom made and although all the data is stored in an SQL database we cannot access that data directly and have no option but to use this custom database software to access it.

In my first macro all I did was to simply get the macro to tab around the fields but this required that the main record screen was maximised and required the user to put the cursor in a specific field to start off with. This to has accessibility issues so I'm now wondering whether I can get Macro Scheduler to find a specific field without the need for tabbing or using mouse co-ordinates?

I guess I could use image recognition to find the text at the side of the field but I don't know how I could then move into the field itself in order to copy the text in it. Ideally it would be nice to be able to read the name of the field itself (rather than the text name next to it) but I'm guessing that would mean going via SQL.

[gdyvig]

Hi RNIB,

Is the problem that the Daisy Consortium provides software of people with disabilities so they can read or listen to books but does not provide a way of selecting the books. The EasyPublisher software was not specifically designed for the disabled but for the general population?

You should report the erratic tab behavior and inconsistent mouse support to EasyPublisher if they are interested in improving their product.

Is there any particular reason the main record screen should not be maximized? That is the best way to make sure all the tabs are visible to simplify Image Recognition portions of your script.

Use the Image Capture Tool to create bitmaps of the field labels. Use the FindImagePos command to find x/y coordinates of the fields for the mousemoves. Macro Scheduler makes this very easy.

If you need the screen to not maximized create bitmaps for the scroll buttons so you can move the display around until the fields are found. This will make your script more complex, you may need to write Repeat loops or subroutines to make this more manageable.

Let us know if you need more assistance.


Gale

[RNIB]

Hi Gale,

The problem isn't actually with EasePublisher (this time) but with a database that we use internally that stores information about the books that we produce and that we then need to copy and paste into certain meta data fields of EasePublisher. As I say, the problem is entirely with our database and all because funding was pulled from the project before it was completed - don't ask it's a really long story

There isn't any real reason to not maximise the main record window it's just that by default this window doesnt fill the screen and you don't see anything else by maximising it. I was just trying to remove one extra click or button press.

I have been playing around this afternoon with image recognition and started off capturing just the field labels but couldn't then work out how to get the mouse to move to the field itself. Should I be capturing the field labels and the field itself, but then as the text in the field will be different each time wouldn't this stop the image recognition?

Cheers

[gdyvig]

Click on just the field label. The field may be in a variety of states (empty, with data, highlighted, unhighlighted).

Make note of how the application exactly behaves when you click on a field label. Can you immediately start typing? Must you click inside the field. What must you do to clear text already in it?

Let's say you need to move the mouse to the field and start typing. Your code would look something like this:

Code: Select all

//This code has not been tested
//color tolerence
Let>ct=10 
//returncenter (0 means return upper left corner)
Let>rc=0
//lets say myfield is 200x20 pixels directly to the right.
Let>Xoffset=210
Let>Yoffset=10
FindImagePos>myfield,SCREEN,ct,rc,Xarr,Yarr,NumFound
if>NumFound=1
  MouseMove>Xarr_0+Xoffset,Yarr_0+Yoffset
  LClick
  SendText>What I want to type
endif

Does this help?

Gale

[RNIB]

Ahh yes I think I follow this.

The trouble is that you do actually have to click in the field box before you can do anything and there is zero consistency to what then happens. Sometimes clicking in the field will select all of what is already there, sometimes it doesnt and sometimes it might only select part of the text that is already there. Oh and Ctrl+A does nothing!

Like I say, funding was pulled before it was ever completed - yet we are still expected to use it

I have a feeling this may take a while!!

[gdyvig]

On some applications we have that problem too.
Using Ctrl-A to highlight a field is inconsistent when used manually?
Do you have to drag the mouse to make highlight the field.

Try one of these methods to clear the field:

Method 1:
Click just inside the left edge of the field.
Press the Delete key a large number of times.

Method 2:
Click just inside the right edge of the field.
Press the Backspace key a large number of times.

Method3:
Click just inside the left edge of the field
LDown (press and hold the mouse button down)
mousemove to just inside the right edge of the field
LUp (release the mouse button)
The field should now be highlighted.
Press the delete key
The text should be cleared.


Any of these methods work?
If not, try adding wait statements.


Gale

[jpuziano]

Hi RNIB,

Here's another way to clear the field that may work for you. Its less sensitive to exactly where you click within the field... and hopefully will work the same way for you whether you have data in the field or not:

click anywhere inside the field
Press End
Press Shift
Press Home
Release Shift
Press Del

Take care

[Bob Hansen]

Rather than dealing with screens and fields, is it possible to use SQL commands to get the data? If this is truly a SQL database, then there may be a couple ways of getting the data out, including a complete listing of all the tables and fields.

If you can't use SQL sommands directly, does the application have the ability to dump out all data into an ASCII file? Then you can use SQL commands against that as a database source.

[gdyvig]

Hi RNIB,

I see you are not using EasyPublisher, but Dolphin EasePublisher (Also called Dolphin Publisher) and that Dolphin is in the space providing accessibility solutions.

More about Dophin:
http://www.yourdolphin.com/

Their user guide is available online if anyone wants to help on this. Download from this site:

http://www.yourdolphin.com/productdetail.asp?id=12

I notice EasePublisher makes extensive use of hotkeys for navigation, you may be able to take advantage of these. The hotkeys do not seem to extend to the field level in all cases.

They have offer Dolphin scripting tool that adds accessibility features to other applications. I claims it can alter the tab sequence of fields for any application. Perhaps that can solve your tabbing problem.

They also use markup sheets to customize the EasePublisher UI. The customization project that ran out of funds must be modifying these markup sheets.

I saw no reference to an API that could be used with Macro Scheduler's LibFunc command. You may want to inquire about that.


Gale

[RNIB]

Thanks everyone for your replies, I have been away for a couple of days so have only just read them and will try many of the ideas suggested.

Just to clear up a couple of points though. The problem isn't with EasePublisher, we have no problems at all with navigating this with a macro or with entering any data the problem is entirely with a database that we had created for us by a third party that we use to store information on our books for internal use only. This database does contain certain information that we need to then add to the meta data fields in EasePublisher (EasePublisher is it's original name but now it is called Dolphin Publisher). This is simply a case of copying the contents of fields in our database and then pasting that into the relevant fields in EasePublisher/Dolphin Publisher. We do not want to delete this information from the fields in our database but to simply copy it and paste it into EasePublisher.

Unfortunately our database was never finished and although it does access an SQL database on a server we have no way of running any SQL statements and sadly there is also no export function and so we are limited to simply copying the contents of a field.

The first macro that I wrote to automate this simply used a lot of Press TAB * 32 etc to move around to the correct fields in our database but the problem with this is that it requires the cursor to be placed in known position first to start the macro from and this is where the accessibility issue comes in. Because the TAB order is totally wrong it makes accessing a known field in the first place very difficult for a visually impaired or blind user. Hence why I was wondering if I can get the macro to simply read the field names so that it could automatically locate the relevant information without requiring the user to place the cursor in a particular field first.

[Marcus Tettmar]

Unfortunately our database was never finished and although it does access an SQL database on a server we have no way of running any SQL statements and sadly there is also no export function and so we are limited to simply copying the contents of a field.

But Macro Scheduler gives you commands that let you issue SQL statements. What kind of server is it? If you have a desktop app that you usually use to access this data then you DO already have a connection to it and you must have the required drivers. Therefore Macro Scheduler can also access this data and you can issue SQL statements from Macro Scheduler code.

[RNIB]

Unfortunately our database was never finished and although it does access an SQL database on a server we have no way of running any SQL statements and sadly there is also no export function and so we are limited to simply copying the contents of a field.

But Macro Scheduler gives you commands that let you issue SQL statements. What kind of server is it? If you have a desktop app that you usually use to access this data then you DO already have a connection to it and you must have the required drivers. Therefore Macro Scheduler can also access this data and you can issue SQL statements from Macro Scheduler code.

Not knowing much about SQL (my only experience of it is in using CMS systems for websites like Wordpress, Joomla etc) wouldn't this require knowing login usernames and passwords for the SQL server and the names of the database fields? We have no access to the SQL server itself and despite lots of pleading from those that do have this information they won't release it to me for "security reasons"

[Marcus Tettmar]

You use an app that has access, so you must also have access. You only need the same level of access that the app has.

Never mind. Obviously your limitations are political or due to lack of documentation rather than technical. It was just a suggestion.

Earlier you were asking how to identify a field via image recognition and click in the field if the label is not clickable. What we do in these situations is capture the label AND the first part of the edit field together. Then from the coordinates returned you can calculate a position that is at the right hand edge of the capture and click there. That would ensure you click inside the field (assuming the field is to the right of the labels - but same principle applies for other configurations - capture label and a bit of edit field together). Or you could just capture the label and click a set amount to the right of it, although arguably that's not as reliable as you are using an assumed offset.

[RNIB]

You use an app that has access, so you must also have access. You only need the same level of access that the app has.

Ahh yes of course - sorry being thicker than usual there!

As you say, a lot of my problems are political. I don't work in our I.T. department and so certain areas I am not able to access and because MacroScheduler isn't used by our I.T. Department I can't get them to do anything with it or help me in any way. Therefore I'm pretty much on my own trying to get something to work simply because nobody else wants to take the responsibility to do it themselves. Oh and yes it really does annoy me!

Anyway, each member of staff does have a login account for the database application so presumably I could just use my username and password and would therefore just need the URL or IP address of the SQL server in order to be able to run SQL statements?

As I say, I have no experience of SQL and don't know anything about writing SQL statements but am happy to try and learn, the only question I have in this regard is how do I get MS to issue them, I couldn't see anything on SQL in the help files etc?

Earlier you were asking how to identify a field via image recognition and click in the field if the label is not clickable. What we do in these situations is capture the label AND the first part of the edit field together. Then from the coordinates returned you can calculate a position that is at the right hand edge of the capture and click there. That would ensure you click inside the field (assuming the field is to the right of the labels - but same principle applies for other configurations - capture label and a bit of edit field together). Or you could just capture the label and click a set amount to the right of it, although arguably that's not as reliable as you are using an assumed offset.

Assuming I can't get the SQL stuff to work (quite likely probably) then this does sound like a good option.

Cheers for the help, much appreciated.

[Marcus Tettmar]

The login used for the app isn't necessarily the same credentials as for the SQL server.

I'm wondering which version of Macro Scheduler you have because if I open up the help file and click on SQL in the index I am shown the DBExec and DBQuery commands.