Query Examples

Calendar Builder of Social Good Software is a tool designed to create calendars from Altru data. It is useful in creating calendars for time ticketing, school programs, and special events. Learn how to add additional programs to your calendar and make necessary adjustments using the Altru query and enjoy the benefit it can bring to you and to your organization. 

Adding Additional Programs to the Calendar

1: Go to your Social Good Software account.

2: Proceed to Calendar Builder and click on the existing calendar where you want to add events.

Adding a program to the query of Timed Ticketing Calendar

1:  Go to Settings and click the query for Timed Ticketing under Events to start and make necessary adjustments.

2: It will redirect you to Altru’s Ad-hoc Query wherein you need to click Edit Definition.

3: You can now enter the program that you want to add to your query:

• Go to Program Events then under Fields click Name and drag to Includes records where section.

• A dialog box will pop out where you can put the name and criteria of the event you want to add. Click OK. See the sample below.

• You can now make adjustments to your Event query. For illustration, we made the following adjustments:
  • The newly added event is moved up just after the old one.
  • OR was used instead of AND
  • Added open and close parenthesis () from the beginning of the old event to the newly added event.

• Click Preview Results to check if the query with the newly added event is working. Click Save and Close if it is to your preference.

Adding a program to the query of Reserved Events

1: Go back to Calendar Builder. Click on the other query under Reserved Events. This query will show you if the event tickets were sold out or not.

2: Same with the first query, just click the link which will prompt you to Altru’s Ad-hoc Query where you need to click the Edit Definition.

• Go to Sales Order Item and select Sales Order Item Ticket then Program Events. On the Fields section select Name and add to ‘Include records where’ which will redirect you to a dialog box. Set up the criteria to Contain and put the name of the event you wanted to add. Click OK.

• Back to “Sales Order Item Ticket”. Select “Tickets” and on the “Fields” section drag Status to “Include records where” in which a dialog box will appear. Just set it up as “Equal to Value” and “Active”. You also need to add the Status under the “Results field to display” and add COUNT

• We will make the following adjustments to the query on this example:

Under Include records where:

• From AND to OR
• Put open parentheses before the Sales Order Item of the existing event and close parentheses at the end of the added event.
• Remove Refund status is equal to 0

Under Results fields to display

• Remove the Quantity
• Click on Save and Close once all is done and good.

Syncing of Data

1: Back to Calendar Builder. Go to Sync Activity and click on Request Data then another Request Data on the dialog box. This will sync over all the information based on the recently made query into our calendar.

2: Once it’s done syncing, proceed to Settings then click on the gear icon and select COUNT(Sales Order Item Ticket\Tickets\Status) before you click Save.

3: Click Preview to check your output. From here, your calendar will automatically update on your website if you have it embedded. We ask for your patience when doing this because it takes a minute or two before it reflects on your site.

If you have more questions please reach out to us through our support channel.

The availability for a calendar is not queryable. Altru doesn’t allow us to query so we found a way to compute it. We need to calculate the availability manually to accurately account the number of particular tickets left. This guide will show you exactly how to calculate availability in the calendar so keep on reading.

Calculating the availability in the calendar

1: Make sure that you already have the Calendar Query which is the output of all program information. This query gives the number of capacity you have set for a specific event.

2: We also need the Reserved Event Query to calculate the number of tickets sold. The query includes tickets that are sold and not refunded. Under this query, we’re outputting quantity.

3: Grab the number of capacity that was set based on the result of the calendar query less the number of tickets sold and the difference is the availability. This give us the formula:

Calculating Sold

Use the formula above to calculate the availability but before you do that, determine first the number of tickets sold. We will base the total number of tickets sold on the output given by the reserved event query. The query will display the sales order with the total quantity of tickets for a particular event. You can calculate the number of items sold by taking the sum of all quantities in the output query.

By taking the capacity based on the Calendar Query and the quantity calculated by taking the sum of all quantities displayed on the sales order provided by the reserved event query, you can now calculate the number of availability for a calendar.

Frequently Asked Questions:

1. What are the reasons for an incorrect quantity displayed on the calendar?
   • The probable reason for an incorrect quantity displayed is refunds, partial refunds, cancellations, rescheduling and so on. Because of that,  you need to explicitly exclude refunds in your query.
   • Include “Refund status is equal to 0” in your query to acquire data on tickets not refunded. 
   • You can also remove the ‘refund status is equal to 0’ if you wish to include refund on the calculation for sold.
   • To troubleshoot this issue, you need to output the specific date with specific time for both queries. After that you can now calculate the availability.

2. Can I manually override availability and mark it as sold out?

• Yes, you just need to go to your program and set the capacity to zero (0) and that will automatically pull the items off for you.
• To remove those partially refunded tickets to your calendar, just go to that particular event date and time and set the capacity to zero.

Create a reserved events query with Altru to show the availability in your calendar. Follow this guide to understand how the calendar updates with availability and learn the use of capacity. 

Process on how to get the availability

1. Create two queries for a calendar of specific event.

• Calendar query (Query #1)
  • It is a query that will show you the current number of tickets or items that you set up for a specific event in Altru. It will give you the capacity for each time slot and hour.

• Reserved events query (Query #2)
  •  It is a query that gives you the record of all the items sold in which you need to add them all.

2. Subtract the total number of sold items in query 2 from the capacity in query 1 to get the availability. 

Note: Repeat the process for every date to get the availability.

You need to do the manual computation to get the availability in your calendar because Altru doesn’t give us the ability to query it. It is a process that we found for you to still be able to show availability in your calendar while working around Altru’s limitations. Though it is not a perfect solution and the process is not always accurate, it still gives you a result close to the actual availability number.

Process of creating a reserved events query:

1. In Altru, click the Add an ad-hoc query.

2. Select Sales Order inside the dialog box because you need to get the sum of all the items sold. Remove any query before you start a new one.

3. Go to Sales Orders > Sales Order item > Sales Order Item Ticket > Program Events

4. On the Program Events, you need the following:

• Results Fields:
  • Name
  • Start Date
  • Start Time
  • End Time
  • Quantity (back to Sales Order Item)
  • Sales Order Record (back to Sales Orders). A query to get the sum of multiple items being purchased.

5. Next is to restrict the following query:

• Start Date 
  • In this demo, we will make it a rolling calendar which means past things are not included.
  • Set it as On or After
  • The Specific date is Today. Click OK

• Name
  • Set to ‘Equal to’
  • Museum Admission

Note: You might have a different name for your program so change it accordingly to be able to display the transactions. This is just for illustration.

• Refund Status
  • Back to Sales Orders and drag it to ‘includes record where’ area then set it as equal to zero (0). This means that no refunds happen on these items.

• Sales Order Status
  • Set it to ‘One of’ then ‘complete’ and ‘confirmed’.

Note: Reserved is also in use depending on your organization. Know that reservation is done when a guest reserves a date and time over the phone without payment until the guest visits in person. You can add it if your organization is making a reservation over the phone to include it as the status of your Sales Order. 

6. Give the query a name before you Save and Close.

7. Copy the OData link of the previously created reserved events query and proceed to the calendar that you are working on then click +OData Link.

8. Paste the previously copied link then click Next. Wait as the app validates the link.

9. Click ADD LINK after it validates the link.

How to check if the Calendar was Configured Correctly

1. Go to Reserved Event Query and click the little gear.

2. An option will pop out that you can set up to make sure that you configure your calendar correctly.

• Max Capacity Reach. It is an option to hide or show the sold-out events. 
  • Hide the events. The sold-out events will no longer be shown on your calendar.

    

  • Show the events. You can customize the look of the sold-out events on your calendar.

    

Note: The important part is the mapping. Make sure that the value under Quantity Count is the same as the query made with Altru under Result Fields. 

The total item sold is calculated based on the items processed within the sales order so discreetly check if your calendar is mapped correctly. 

3. Click Save Settings once all set.

4. Proceed to Event Name and put on the ‘availability’ to show it to your calendar for the given event and time based on the calculation made with the reserved event query.

Once you have your reserved event query, you will now be able to see the capacity and quantity on your calendar. You can also adjust your query and make revisions to suit your organization’s preference. If you have more questions please reach out to us through our support channel.