Question

Photo of Cody Tindall

0

Can I use Data Views with an SQL statement?

7

If I have a data view setup, can I use that to filter an sql query without having to rebuild the data view as a WHERE statement? If so, how would I call/reference the data view? If not, is there a way to pull the sql statement generated by the data view?

 

Thanks in advance!

Cody

  • Photo of David Leigh

    0

    Basically, I think the answer to your question is No, it's not possible to re-use a Data View in the scenario you describe.

    However, now I understand what you are trying to do, I have a suggestion for an alternative.
    Rather than importing the notes, history, etc to Google Contacts, perhaps you could consider taking advantage of Rock to provide that information directly?

    If you add a "Rock URL" hyperlink field for each Contact you import into Google, your staff could use this to link back directly to the Person's record in Rock, giving them access to all the information they need. You could construct the link in a Report field using Lava to join the base URL of your site with the PersonID, so that each Contact record has a URL like "https://www.rockrms.com/Person/{PersonID}".

    I haven't actually implemented this solution myself yet so I can't claim it will solve your problem, but it's on my to-do list - and if you try it and it works, I'd be happy to hear about it!

  • Photo of David Leigh

    0

    Hey Cody,

    Are you able to provide a more detailed scenario in which you would want to do this?
    I'm not sure if you are trying to do this in C# code, in Lava, or in some other context.

     

  • Photo of Cody Tindall

    0

    @David

    Yes, our church currently uses Google Contacts (somthing I would like to get away from in the future and replace with an app that syncs to Rock's database) for all the staff phones to get updates. We've finalized our data in Rock this past month and are trying to make rock our primary database. So, once a week I have to export all the active data from rock and import it into Google Contacts. I built a data view that filters the Contacts the way we need them and I built a report that captures the main information we need to come across. The report however doesn't offer a field to include the notes on each profile which we're using to log visitation notes and family history. The quick solution was to make an SQL query in a dynamic data block and pull in all the fields I need that way but I was wondering, instead of retyping all the data view information under a WHERE statement, if I could just call that data view?

    I hope its a little clearer, sorry about that.

    Cody

  • Photo of Cody Tindall

    0

    I tried your suggestion, but it doesn't look like Rock exports the liquid fields... I put this in for the liquid column "<a href="https://rock.faithfulbaptist.com/Person/{{ Id }}">{{ FirstName }} {{ LastName }}</a>"  And I got this in the export, "Data _ Liquid _16: 0". I know there are a few things like this that you just can't take out of the Rock enviroment (profile pictures are another one) I guess the liquid columns are one of those. I'm going to keep working on a solution for this but our staff knows that the end game is to have an app on their phones that pulls directly from Rock's database so that's probably where i'm going with this. Let me know if you have somthing else I can try!

  • Photo of David Leigh

    0

    Cody,

    I tried this myself, and you are right - the Excel Export of Liquid/Lava fields is broken. This looks like a bug to me, but I have a feeling the Export to Excel feature is likely to be superseded anyway, replaced by the Merge Templates feature available in upcoming V4. I'm not sure if you have seen this (it's only on the develop branch right now) but using a Merge Template, you could enter the literal part of the URL in the document itself and simply merge the {{ Id }} field, then use the output file to import into Google Contacts. No much help to you right now I know, but from what I understand of the most recent podcast from the Rock team, V4 will see the light of day fairly soon. Sorry, but that's the best I can offer right now!

  • Photo of Arran France

    0

    Hey Cody,
    I'd be interested in creating a plugin to sync the data between Rock and Google Contacts as we rely very heavily on the Google Apps suite as well. 

    I'll put this on my to do list, take a look at it, and then see how feasible it is.

    • Cody Tindall

      Cody Tindall

      Arran,
      That would certainly give Rock an incredible edge as there isn't a single church management system that syncs with Google contacts, that I know of! If there's anything I do to help you with this, let me know.

      • Arran France

        Arran France

        Hey Cody,
        After a bit of thought this would probably end up being something we didn't use however this would definitely make an interesting project. That said I've had a look at it and I'm pretty sure this is feasible. Could you drop me an email at arran@hopecorby.org to help me figure out the scope of things and what specific features this would need?

      • Photo of Michael Garrison

        0

        Back to the original question for a moment, the idea of grabbing the result of a Data View from a SQL query is something that would be useful. I can see literal workflow as being problematic, but as another possibility (and to assist those of us who are familiar with SQL but don't exactly speak it natively), I wonder if it would be possible for administrators to see the SQL query that is generated by a Data View- potentially as a hidden-by-default block on the Data View page itself?

        That way we could build as much of a report as is possible using the simple Data View interface provided by Rock, and actually use that as a starting point to expand just the "one more level" to add something that the Data Views can't do...