Question

Photo of Cody Tindall

1

Running reports that display fields from multiple tables

2

I'm trying to run a report (or even an sql query) that is filtered based off a custom attribute (Mailing List: Yes). But I want the report to display information that is stored in the Person, Location, and "table_where_custom_attributes_are_stored". (Example: Last Name, First Name, Custom Attribute, Street 1, City, State, Postal Code)

I need the last name for sorting, the custom attribute is how we want their name formatted for mailing, and I need the address broken down like this for formatting.. the Address field in the Person table won't work because merging Address just puts their mailing information all on one line.

I know these tables are linked with Id numbers and can reference each other but i'm having a hard time with this one.. Thanks in advance!

  • Photo of Rock RMS

    0

    Here's a SQL statement that does what you want but reports on the Baptism Attribute. You would need to change the [AttributeId] = 174  to be the Id of your attribute.

    SELECT
	[NickName]
	, [LastName]
	, (SELECT [Value] FROM [AttributeValue] WHERE [AttributeId] = 174 AND [EntityId] = p.Id) AS [Baptism Date]
	, (SELECT [Street1] FROM [Location] WHERE [Id] = (SELECT TOP 1 [LocationId] FROM [GroupLocation] WHERE [GroupLocationTypeValueId] = 19 AND [GroupId] = (SELECT TOP 1 [GroupId] FROM [GroupMember] gm INNER JOIN [Group] g ON g.[Id] = gm.[GroupId] WHERE [PersonId] = p.[Id] AND g.[GroupTypeId] = 10))) AS [Street 1] 
	, (SELECT [Street2] FROM [Location] WHERE [Id] = (SELECT TOP 1 [LocationId] FROM [GroupLocation] WHERE [GroupLocationTypeValueId] = 19 AND [GroupId] = (SELECT TOP 1 [GroupId] FROM [GroupMember] gm INNER JOIN [Group] g ON g.[Id] = gm.[GroupId] WHERE [PersonId] = p.[Id] AND g.[GroupTypeId] = 10))) AS [Street 2] 
	, (SELECT [City] FROM [Location] WHERE [Id] = (SELECT TOP 1 [LocationId] FROM [GroupLocation] WHERE [GroupLocationTypeValueId] = 19 AND [GroupId] = (SELECT TOP 1 [GroupId] FROM [GroupMember] gm INNER JOIN [Group] g ON g.[Id] = gm.[GroupId] WHERE [PersonId] = p.[Id] AND g.[GroupTypeId] = 10))) AS [City] 
	, (SELECT [State] FROM [Location] WHERE [Id] = (SELECT TOP 1 [LocationId] FROM [GroupLocation] WHERE [GroupLocationTypeValueId] = 19 AND [GroupId] = (SELECT TOP 1 [GroupId] FROM [GroupMember] gm INNER JOIN [Group] g ON g.[Id] = gm.[GroupId] WHERE [PersonId] = p.[Id] AND g.[GroupTypeId] = 10))) AS [State] 
	, (SELECT [PostalCode] FROM [Location] WHERE [Id] = (SELECT TOP 1 [LocationId] FROM [GroupLocation] WHERE [GroupLocationTypeValueId] = 19 AND [GroupId] = (SELECT TOP 1 [GroupId] FROM [GroupMember] gm INNER JOIN [Group] g ON g.[Id] = gm.[GroupId] WHERE [PersonId] = p.[Id] AND g.[GroupTypeId] = 10))) AS [PostalCode] 
FROM
	[Person] p
    • Cody Tindall

      Cody Tindall

      This returns an error message when placed in dynamic data block:


      Query Error! Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Query Error! Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    • Photo of Joe Carroll

      0

      I'm not familiar with how the custom attributes are implemented, but this SQL should give you an idea of how to join the other tables that you need.  I need a way to produce address labels by family so yesterday I dove into the database to try and figure out how the different tables relate.  Eventually, I'll remove the person information so that the SQL only returns one row per family.  My only warning is that while this appears to be producing a list of active adult members/visitors/attendees with their home address (broken out by street, city, state, zip), it's also my first attempt at querying the DB directly so it might still need a tweek or two.

       

      SELECT P.FIRSTNAME
                   ,P.LASTNAME
                  ,(SELECT "VALUE"
                      FROM [DEFINEDVALUE]
                   WHERE DEFINEDTYPEID = 4
                        AND ID = P.CONNECTIONSTATUSVALUEID
                   ) AS CONNECTION_STATUS
                  ,G.NAME
                  ,L.STREET1
                  ,L.CITY
                  ,L."STATE"
                  ,L.POSTALCODE
        FROM [Person] P
          JOIN [GROUPMEMBER] GM
             ON P.ID = GM.PERSONID
          JOIN [GROUP] G
             ON GM.GROUPID = G.ID
          JOIN [GROUPTYPE] GT
             ON G.GROUPTYPEID = GT.ID
          JOIN [GROUPLOCATION] GL
             ON G.ID = GL.GROUPID
          JOIN [LOCATION] L
             ON GL.LOCATIONID = L.ID
       WHERE G.GROUPTYPEID = 10 --FAMILY
             AND GL.GROUPLOCATIONTYPEVALUEID = 19 --HOME
             AND P.CONNECTIONSTATUSVALUEID IN (65,146,66) -- MEMBER,ATTENDEE,VISITOR
             AND P.RECORDSTATUSVALUEID = 3 --ACTIVE
             AND GM.GROUPROLEID = 3 --ADULT