Xero

Hi Tuan,
My colleague has prepared another sample file for you - stripped down to essentiial data only - and some notes as to how he did so. We have tested importing this into Xero and it works.
Notes:

• =11.0ptThe discount can only be entered as a % reduction in the unit amount. So in the file 10 = 10%. So this field needs to be calculated for the download, assuming a coupon is used with a £ amount - to convert that into a % effect per ticket in that booking (the more tickets in the booking the smaller the % would be).
• =11.0ptEverything else should be direct from Events Booking.
• =11.0ptSome of the fields are text fields that we would like to be configurable - either as global variables in soome cases or as event variables in others or as booking variables in others. This means some configuration options needed and some user fields to fit the Xero download.
• For the example we calculated tax at 20% hence col Y = 5/6 of col X - just so you know how we got that value.
• Our understanding is col O (Total) = (Quantity x Unit Amount) - Discount as %
• Reference could be a free text field input by the user, as a user variable, when they book.
• *Description should be the Event Name
• Invoice should be the invoice number -
• We would like to specify the Account Code per event - as a text field. This will tell Xero how to allocate the incoming payments (it will be communicated to the Event manager to input into Event Booking when setting up the event).
• *Tax type is =11.0ptalways ‘20% (VAT on Income)’ [text string] that should be a global variable - the same for all events but changeable if the government change the rate
• TrackingName1 should be 'Committee' - set as global variable
• TrackingOption1 should be 'Social' - set as global variable - these two allow us to use the website for other revenue streams in future - which might use "sales" and "gloves" for these two fields.
• TrackingName2 should be the Event Category name - selected as part of event specification
• TrackingOption2 should be a user field to indicate the users preferred payment method (eg cheque or BACs) - where payment is "offline" as far as Event Booking is concerned - or "paypal" if paypal - as selected by the member when they make the booking. If problem then use TrackingName2 and TrackingOption2 to capture payment info - Event category is 'nice to have' but not essential.
• Currency should be 'GBP' - drawn from the Event Booking configuration default currency but converted into standard currency code
• You can use any invoice descriptors, but please avoid any using the format ‘INV-xxxx’ as this is the default used by Xero and will likely lead to duplicates. Our setting in EB is IV with 5 digits - which should be fine.
• =11.0ptIt is best (and simpler) to have all amounts as inclusive of VAT. We have updated the file to reflect this.
• =11.0ptWhen the file is loaded into Xero any data already loaded will cause duplication - so we need menu dialog control to specify start date, end date (range, if not today) and event (if not all events). Ideally the system should retain the previously selected range and display for information when next accessed.

=11.0ptWe hope all this is doable and look forward to working with you to achieve it.
Best wishes,
Andrew

Hi Tuan,

I am thinking how we can make this process more generic - so it can be something useful to all of your customers and give you more sales as a result.

So how about a general 'User Output Report' module?

My idea is as follows: give the users the ability to specify any number of output reports. Just like specifying events.

The reports would have any number of columns. Columns could be fixed (eg some string) or from any data item in the system, including user variables, from global data (eg currency), event data (eg event name), booking data (eg number of tickets purchased by user X for event Y) or attendee data (eg person name). Column specification should include column header text (if different from field name or title - ie Xero requires specific format column headers), maximum width (characters), data format (including prefix or suffix characters like £,$,%).

The report specification might also include who can run it (user group permission) and which data they see (my events only or all events). In the case of Xero it is not relevant to event managers but to the accountants - who need to be able to see everything.

Users should be able to select up to three columns for sort order as 1st, 2nd and 3rd sort keys, ascending or descending in each case. eg (1) By date, by event, by booking or (2) by event, by booking, by attendee.

For our purposes with Xero we need the row type to be per booking. I can see other row types might be attractive for other reports - eg per attendee (would allow output to link to seating plan software), per Event would allow total values for different events to be compared (eg number of coupons used), per date would allow website booking traffic trends to be monitored (eg daily booking total), per-Event-per-date would allow trends per event (early vs late bookings). So the ability to specify row type would be useful to many people, and the ability to group by event also.

These reports could either be linked to menu buttons individually or could have a single menu button to open a dialog box to specify the parameters for the report when it is run. The dialog box would allow the user at run time to limit the report to a specific date range [from/to] and to specific events eg [one event/specific events/my events/all events/future events]. Everything else is configuration options when the report is specified.

I think the output filename should always be the name of the report with the date/time it was run - to the second - so no two output reports from any one user would ever have the same file name.

That way rather than you specifying one report for us; instead you make a tool that allows us to do it ourselves, and then anyone could use it for any purpose. How much more complicated would that be and do you think it would be worth doing that way, if we were paying the part of the cost as if for a one-off report? What do you think?

Regards,
Andrew

Hi Andrew

I have to say that it is complicated to build and it's not worth to spend time working on something flexible like that

Tuan

Hi Andrew

I have to say that it is complicated to build and it's not worth to spend time working on something flexible like that

Tuan

Hi Tuan,
That's fine. It was just a suggestion. As long as we get the Xero file we are happy. Do you have an idea yet what that will cost and how long it will take to build?
Thanks for your help.
Andrew

Hi Andrew

I still haven't had time to review the format yet. I just come back to work from new year holiday (new year holiday in our country and some other Asia countries just ended yesterday), so I have to work to clear support queue first. You might have to wait me until this weekend to have the quotation

Tuan

Hi Tuan,
That is fine, thank you.
Best wishes,
Andrew

Hi Tuan,
We have an internal meeting tomorrow afternoon to discuss the status of this project. it would be very helpful if I had something I can report re cost and timeframe. If it is not possible or too expensive then we will have to explore other alternatives, and we plan to go live in April. So I really hope we can make something work.
Best wishes,
Andrew

Hi Tuan,
A small modification to the specification. We don't need the event category in Xero, so would like to have fields TrackingName2 and TrackingOption2 as variables that can be assigned in the global specification, to any user variable. Specifically we would like to create a radio button for the user to inform us how they wish to pay (in Xero) and a free-form text field for them to type notes for us, eg something like "payment in two amounts by cheque from person X and person Y" to help reconcile incoming amounts with invoices. It is probably easier to use user variables for this than you create additional system variables, but we will accept your advice on that question.
Regards,
Andrew

Hi Andrew

I just looked at the formated report and there are something which I don't understand:

1. Reference: What will be used for that column?
2. Due Date: Where we can get data for that date?
3. UnitAmount: Is this the price of the event?
4. Quantity: Is this number of registrants?
5. Stil unclear for me how Discount data will be calculated?
6. How TaxAmount will be calculated? Base on fixed Tax Type (20%)?
7. Where to get data for  BrandingTheme ?

This should not a hard work, but we need to understand what data will be used for what column.

Tuan