Integration with Excel and other Desktop Applications

Posted on December 15, 2013 by ret56fe

OurCarClub offers a few different ways to access your data and use it in other applications. The most common purpose for this facility is to manipulate the data in Microsoft Excel, to generate special purpose lists, or to perform analysis of the data, such as charting or pivot tables.

The two basic methods of extracting data involve taking a copy of the data that will be static, and the third, more advanced option creates a live link to the data in OurCarClub, that can stay up-to-date with subsequent changes made. Each of these can be accessed from any List page.

Option 1 – Export as CSV

When this button is clicked, a dialog box will prompt you to save the file. It will be in CSV (or “Comma Separated Values”) format, which can be opened directly in Excel. The file will be named for the list being viewed, i.e. persons.csv or vehicles.csv

Option 2 – Copy and Paste to Excel

Under the link labelled “More…” are some less commonly used Export features.

In this instance, the “Show as Plain HTML” is what is required. This will re-display the current list, but in a very basic format.

In your browser, under the “Edit” menu, choose “Select All”, then “Copy”. Switch to Excel, and “Paste” into a Worksheet.
Note that this method is probably not well suited to lists that are likely to generate thousands of entries.

Option 3 – Access live OurCarClub data from Excel using a "Web Query"

OurCarClub provides a method for integrating with other web-apps or applications. By using this feature, you can create a link to live data that can be refreshed at any time, bringing the latest Member or Vehicle data into your spreadsheets whenever you open them.

This feature requires that you generate an API Key. API Keys are not generated by default for all OurCarClub users, as they are an advanced feature. An API Key should be treated like your username and password, and not shared with others.

To create an API Key, click on your username in the top right corner of the application.

Generating an API Key

On the page with your details, there is a box labelled API Key, and a button to Generate a new API Key. This is used to create an initial key, or to regenerate it.
NB: If you regenerate your API Key, the old one is no longer valid, and anything that uses it will need to be updated.

The key is a long string of letters and numbers. For the current purpose (creating an Excel Web Query) it is not necessary to make a note of it or copy it anywhere else.

Create a Web Query File

Once again, back to the List view. Now, under the “More…” export options, there is an additional “Save as Excel Web Query” option. Clicking this will result in a “Save As” dialog box appearing, for saving the file on your hard drive. By default, it will be called “OurCarClub-<club>-<list-type>.iqy”, but you can of course change this to call it anything you choose. The “iqy” extension should be retained, especially on a Windows PC.

Using the Web Query in Excel

All reasonably modern versions of Microsoft Excel have the option to “Get External Data”, and this is usually located under a “Data” menu item. You will want to locate the “Use Saved Query” option, and then navigate to the Web Query file you just saved. You will then be prompted for where you want the data to be placed in the spreadsheet (usually cell $A$1 in the active sheet).

OurCarClub data live in Excel

Once you click OK, Excel will request the data from OurCarClub, and return it as a table of data. You are then free to create graphs, pivot charts or other information from this data. At any time in the future, you can simply refresh the data from the Data menu, and obtain the latest information from OurCarClub.

Committee Meetings and Task Management

Posted on July 20, 2013 by ret56fe

OurCarClub allows you to easily keep minutes of Committee Meetings, including the members who attended and those who gave apologies. More importantly, you can capture new tasks and who has been assigned ownership of them, track their progress and close them once they’re complete. You can also easily print a formal agenda and official minutes.

Committee Meetings

You’ll find Committee Meetings as an option under the “Events” menu. Notice also the “Committee Tasks” option. We’ll come back to that later.

Create a new Committee Meeting

Good meeting procedure means having an agreed agenda. For this reason, the Committee Meeting record should be created before the event. Like most aspects of OurCarClub, click the “Add” button.

Create a new Committee Meeting

The meeting record itself only has a handful of fields.

The Meeting No is automatically generated as the next one in sequence. Meeting numbers are important for tracking the tasks created at them. This is a mandatory field.
Enter the date the meeting will occur – this is also required.
The Start and Finish Times and Location are all optional, and are simply text fields. Obviously the Finish Time cannot be known until the meeting actually occurs, so we’ll edit the record to add that later.
As always, click the “Add” button to save the new record.

Committee Meeting Detail

Now that we can see all the related information, it should be obvious that most of these can only be populated after the meeting occurs.
What we should do now is create an agenda. You can create an entirely customised agenda, but most of your meetings are likely to follow a standard pattern, which will be created for you by clicking “Generate default agenda”.

Default Agenda Items

The meeting now has the 6 standard agenda items. (Later on, we’ll see how you can adjust the default agenda items to suit your club.)
Click “Generate Agenda” to produce a formal agenda document.

Agenda

Here’s our new meeting’s Agenda, ready to be distributed. Print it out a few copies, or print to PDF and email it if you have PDF software installed.

The other thing that should be provided to attendees is the minutes of the previous meeting. (We had a previous meeting – now you know why our newly created meeting was number 2). Click the link under Events to jump back to the list of committee meetings.

Generating Minutes from a previous Meeting

Click on the title of the meeting you want to generate minutes for.

A Previous Meeting

Because this is a previous meeting, we have details of Attendees, Apologies and Tasks created. You might also notice this meeting has a finish time recorded. We’ll come back to all that later.
Click the “Generate Minutes” button to create your document.

Meeting Minutes

All of these minutes are ‘boilerplate’, created when you generated the agenda.
The minutes of the General Business and the Next Meeting would have been added at the time of the last meeting. Obviously these change with each meeting held.
This 7th item gets added on automatically, based on the finish time recorded for the Meeting.
Because this was our first meeting, we had no Task Updates. Otherwise, any tasks still open at the time of the last meeting would show here, with any recorded updates.
We created a new Task at that meeting, which is identified as 1-1 (Meeting #1, Task #1). We can see who took responsibility for it and when it was due to be completed.

Like our Agenda, we can print these out for distribution.

Minuting the Meeting

Your secretary (or nominated person) can minute the meeting as it occurs, if they have access to OurCarClub. The minutes can be transcribed later on, of course. To begin, view the relevant meeting.

Record Attendees and Apologies

Recording of both attendees and apologies is identical, and very like adding a ‘related record’ in other aspects of OurCarClub.

Add an Attendee

Select an attendee (1), and click “Add” (2). (NB – if your club is large, the Person selection will show a search-box rather than a huge long list.)

Apologies work exactly the same way.

Add a new Task

As the meeting progresses, any new Task can be added.
Existing tasks can be updated. NB: Older tasks will have been created at other meetings, and appear here for convenience.

Adding a new Task

Enter the details of the task:

Item Number is automatically generated in sequence.
Give the task a short title
The Create Date defaults to the date of the Meeting
Due Date is optional, but should be entered. When a Task is allocated, it should have a defined date by which it will be done.
Select the member assigned to the task.
Enter additional notes about the task.
Click “Add” to save the task.

Managing Club Assets

Posted on April 13, 2013 by ret56fe

Most clubs own a variety of assets that are used to promote the club, or for members’ benefit. There are obvious items like computers or printers, club banners and perhaps a club trailer or barbecue. Beyond that, here are many other things a club might own: perpetual trophies, record books, documents and of course merchandise: shirts, stickers, badges, even reproduction parts for vehicles.
The OurCarClub system allows you track your assets, quantity on hand, who has custody of them and so on.

Asset Types

Under the Assets option on the Menu, choose Asset Types to see the Asset Type List.
Notice this message on the wall that there is an asset overdue for a follow-up – we’ll come back to that later.

Asset Types List

Here is an example of a list of Asset Types. Other possible types could include:

Trophy
Document/Record

You can add whatever suits your club. Let’s add a new type to cover the club’s flag, banner and similar items. Click “Add a new Asset Type”

Add a new Asset Type

Let’s call this type of item “Promotional Material”.
Note the checkbox. This should be checked if this type of Asset is something you sell, and/or need to keep inventory of. Obviously that’s not the case for the club banner.
Click Add or hit Return to save the Asset Type.

View the new Asset Type

We’re now viewing Asset Type, which presents the information we just entered.

The banner across the top notifies you that the new record was successfully added.
Because this is a brand new Asset Type, there are not yet any Assets of this type. Press “Add” to create one.

Add a new Asset

Enter the relevant details here:

Give the item a suitable name. If it has a model name (IT Equipment is a good example of this) then it’s a good idea to include it. Similarly, if it has an ID or Serial Number, that can also be recorded.
Enter the name of the member who has custody of or is responsible for the item.
You can record the price of the item should you choose. This is usually more relevant for Merchandise items, as is the Stock on Hand column.
A memo field is provided to make a note about the item.
A series of dates to record when the item was acquired, disposed of, or needs to be followed up. All three fields are optional, but if the Follow Up Date is in the past, then this Asset will appear on the Wall (as we saw in an earlier screenshot). An example of how you might use this: imagine the asset was a perpetual trophy, given out at the Christmas Party. You might record a follow-up date of Nov. 1, to remind you to get it back from the current holder.
Click “Add” to save this Asset.

View Asset Type (again)

Repeat the exercise to record other promotional material if you wish.

You can see and search all Assets in the Asset List, by clicking this item on the menu.
To see details of the Asset, click this link.

View Asset

Beyond the simple “Memo” field, you can also record a history of Notes regarding this asset if you choose. A Note is also automatically created when custody of an Asset changes.
Click Edit to record a change of custody.

Change Asset Details

In this example, custody of the banner has passed from Emmett to Joe. Click Save to record the change.

Asset Custody History

Note the auto-generated Note recording the date on which custody was transferred.

Managing Merchandise

Merchandise such as club shirts needs to be kept inventoried and tracked. OurCarClub does not attempt to be a full book-keeping system, but provides a means to track inventory with ease.

In this List View, we have enabled displaying the Price and Stock on Hand fields.
Click on the T-Shirts link to view detail for this item.

Recording Merchandise Inventory Changes

When an Asset is recorded as being of a Type marked as “Is Merchandise”, the additional box show above appears.
To record sale of an item, enter the quantity and click “Deduct”. Change the drop-down to “Refund/Re-Stock” to record a refund or a purchase by the club of more stock. (The “Deduct” button changes to “Add” when you do this.)
To update Stock on Hand to reflect a Stocktake/Audit, simply Edit the Asset record and change the Stock on Hand field.

Once you’re comfortable with this, you’ll have control over your club’s assets in no time!

Granting access to other members, and resetting passwords

Posted on December 9, 2012 by ret56fe

OurCarClub users with sufficient privileges have the ability to grant access to other members.

Granting Access

You can grant existing members access to your OurCarClub system easily. At the bottom of the regular "View Member" page you will find the following panel.

A suggested username is provided, but you have the option to change it. Username must be between 3 and 11 characters in length, and it will warn you if the name you’ve chosen is not available.
Enter a starting password, and confirm it.
You have the option to delegate authority – meaning that this user will also be able to grant access to others, reset passwords and so on.

When you click Add, an email will automatically be sent to the member to let them know their login details. If they don’t have an email address, you’ll have to let them know yourself.

Access Granted

After clicking the Add button, the following messages are displayed, and the Access box shows that the user can access this system. Click on that link to edit or delete this access, or to reset the user’s password.

Resetting Passwords

The usual Edit and Delete buttons allow you to manage this access (i.e. upgrade to Administrator or remove Administrative privilege)
The Reset button will generate a new password and email it directly to the user.

Events Management

Posted on December 9, 2012 by ret56fe

Managing Club Events – Where and When, and Who Participated

Viewing a List of Events

The List of Events view is identical in layout to all other Lists. The Show/Hide columns button allows you to view additional general information about an Event. Click Add a new Event to start recording details of an event, either one already held, or one coming up.

Adding a New Event

Adding an Event is also similar to creation of most other types of records in OurCarClub. Mandatory fields have red titles, and categorisation can be done using drop-down lists.

Select an Event Type. The default values are Meeting, Official Club Run and Sanctioned Event, but you can customise this list to suit your circumstances.
Give the event a name, such as "February Meeting", or "All Holden Day". A Date for the event is also required.
The Event Memo field allows you to record other details about the event. For example, the address, or convoy details, or in the case of an event that’s already occurred, perhaps a record of the food supplied by the club for members (30kg sausages, 8 loaves bread etc.) It could be useful to record such information if this is an event you hold every year. Use the field however you see fit.
If you wish to track attendance points, you can override the default values for Points per Person and per Vehicle set for the Event Type. However, this would typically be left blank.
Co-ordinator and Duration: you can optionally record the name of the Member who is co-ordinating the event, if required. Note this field has a blue label, which means it is not a required piece of information. The Duration records the number of days over which an event was held, and defaults to 1.

Recording Event Attendance

When viewing your event, you can quickly log details of attendees, and what vehicle (if any) they attended with.

As with all View screens, the details of the main item can be viewed on the left. Click Edit or Delete to make changes. (NB: You cannot delete a record that has attendees attached to it.)
Type a few letters of the member’s name and choose them from the drop-down list that appears. See over for more detail.
Click the "Run" button to view an attendance report for this event.

Recording Event Attendance (continued)

As shown on the previous page, type a few letters of the attendee’s name and pick them from the list. In this case I typed blu, and had the options of either Jake or Elwood. Typing elw would also have found Elwood.

On selecting the attendee, their current vehicle(s) appear under the Vehicles heading. If they have only one, it is automatically checked. If they have multiple vehicles, check the vehicle(s) they brought. If no vehicles are shown because none have been recorded for this person, you can add the attendee now, and add the vehicle later on by simply repeating the steps shown above. Multiple attendance records for the same person are silently merged together.
Click Add to save the record. You’ll return to the same screen, and start entering another attendee.
Clicking on the name of an already entered attendee takes you to the Person Attendance record. There are fields there to record additional information about the person’s attendance, if required.