Social Good Software

Creating amazing guest experiences through innovative software solutions for the social good community

Query Examples

How to add additional programs to the calendar

By

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.
add additional programs to the calendar
  • 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.
add additional programs to the calendar
Note: this is just a sample added event and an illustration of how to properly adjust the said program in a query.
  • 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.
add additional programs to the calendar
  • 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
add additional programs to the calendar
add additional programs to the calendar
  • 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.

add additional programs to the calendar
add additional programs to the 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.

add additional programs to the calendar
add additional programs to the calendar

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.

How is availability calculated in the calendar?

By

availability

1: In October 2022, Altru released in this article about a new feature that can be used to query remaining capacity for programs. Feel free to use this field to query the remaining capacity.

You may still refer to the old process of calculating the program availability below.

Calculating the availability in the calendar

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 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 gives 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 a refund on the calculation for sold.
    • To troubleshoot this issue, you need to output the specific date with a 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 from your calendar, just go to that particular event date and time and set the capacity to zero.

How to create a reserved events query

By

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.

The Process to get the availability

In October 2022, Altru released in this article about a new feature that can be used to query remaining capacity for programs. Feel free to use this field to query the remaining capacity or refer to the old process of calculating the program availability:

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

  • Calendar query (Query #1)
    • It is a query that shows the current number of tickets or items 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 availability.

You have 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 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:

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’ Program_Name
    • Example: 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 the ‘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’
    • Select 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. m667y

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. The total item sold is calculated based on the items processed within the sales order so discreetly check if your calendar is mapped correctly. 
Note: The important part is the mapping. Ensure that the value under Quantity Count is the same as the query made with Altru under Result Fields.

3: Click Save Settings once all is 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 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.