Discussions

 View Only
Expand all | Collapse all

Formula Query touserlist error

  • 1.  Formula Query touserlist error

    Posted 11-04-2022 12:57
    I'm sure this is a simple mistake, but I'm getting a "expecting user/userlist but found textlist" error.
    Isn't this the function of touserlist? Field 29 is a user field.


    //list all installers scheduled on this date
    //table ID: _DBID_SCHEDULE, FID 29 = installer, FID 6 = date

    var text myquery= "{6.EX.'"&[Date]&"'}";

    touserlist(
    GetFieldValues(GetRecords($myquery,[_DBID_SCHEDULE]),29))

    ------------------------------
    Anne Martin
    ------------------------------


  • 2.  RE: Formula Query touserlist error

    Posted 11-04-2022 14:16
    OK, so not tested but I think you need to do this.

    //list all installers scheduled on this date
    //table ID: _DBID_SCHEDULE, FID 29 = installer, FID 6 = date

    var text myquery= "{6.EX.'"&[Date]&"'}";


    var text AllUsers = 
    ToText(
    GetFieldValues(GetRecords($myquery,[_DBID_SCHEDULE]),29)));


    var user UserOne= ToUser(Trim(Part($AllUsers,1,";")));
    var user UserTwo = ToUser(Trim(Part($AllUsers,2,";")));
    var user UserThree= ToUser(Trim(Part($AllUsers,3,";")));

    etc

    var user UserTwenty= ToUser(Trim(Part($AllUsers,20,";")));


    ToUserList($UserOne, $UserTwo, $UserThree .... etc up to say $UserTwenty)






    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Formula Query touserlist error

    Posted 11-04-2022 15:41
    That doesn't give any errors, but is returning a blank value.
    Should the $myquery variable be a date type?

    ------------------------------
    Anne Martin
    ------------------------------



  • 4.  RE: Formula Query touserlist error

    Posted 11-05-2022 09:10
    Can you make a formula text field with just this formula and see and post back here what it returns?  Let's see what's in your data.

    ToText(
    GetFieldValues(GetRecords($myquery,[_DBID_SCHEDULE]),29)))

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Formula Query touserlist error

    Posted 11-07-2022 09:42
    I made a new formula text field, and it returns a list of user names in text format.
    I tried creating a user list formula field to convert this, but am stumped.

    ------------------------------
    Anne Martin
    ------------------------------



  • 6.  RE: Formula Query touserlist error

    Posted 11-07-2022 09:46
    Can I see an example of the data that comes back.  Are these peoples regular names or are these usernames.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Formula Query touserlist error

    Posted 11-07-2022 10:25
    It's the first and last names of my users in the format below: 
    John Smith ; Robert Long ; Jane Doe



    ------------------------------
    Anne Martin
    ------------------------------



  • 8.  RE: Formula Query touserlist error

    Posted 11-07-2022 10:29
    OK, so there are probably thousand of John Smith's in the world so it is highly likely that the name John Smith is not unique as someone's name who uses Quickbase.  So the function UserToName does not work on someone's name.

    The field that you need to roll up is the ToText value of their userid or the ToText value of their email address,  and not their regular "John Smith" type name.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Formula Query touserlist error

    Posted 11-08-2022 13:10
    Success! Thank you!

    ------------------------------
    Anne Martin
    ------------------------------



  • 10.  RE: Formula Query touserlist error

    Posted 12-07-2022 16:02
    Mark,

    In a Pipeline we can see their UserID.  How do you get that value in a Formula Query?

    ------------------------------
    Don Larson
    ------------------------------



  • 11.  RE: Formula Query touserlist error

    Posted 12-07-2022 16:27
    @Don Larson  not sure what your question is.​

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 12.  RE: Formula Query touserlist error

    Posted 12-07-2022 22:35
    Edited by Don Larson 12-08-2022 08:09
    Mark,

    Sorry for the poorly crafted question.

    Down in Answer 13.  Prashant has shown what I meant about getting the UserID when using a Pipeline.

    However, my business case matches Anne's.  I need to read a User field in an unrelated table and have that be a User Field in the the record where I am searching from.

    If a Formula Query could get to user_list.id, the the Formula User field would have the value needed.   As you said in 8. we do not need the text value of their name we need the ID.

    I am hoping you have an idea on how to get that with a Formula Query.

    ------------------------------
    Don Larson
    ------------------------------



  • 13.  RE: Formula Query touserlist error

    Posted 12-08-2022 08:35
    Below is my successful (abbreviated) formula. I'm not familiar with how to get the User ID though. Hope this helps!

    //list all installers scheduled on this date. convert to email address, then back to user
    //table ID: _DBID_SCHEDULE, FID 79 = installer email, FID 6 = date

    var text PullUsers= "{6.EX.'"&[Date]&"'}";
    var text UserEmail= ToText(
    GetFieldValues(GetRecords($PullUsers,[_DBID_SCHEDULE]),79));
    var text AllUsers= $UserEmail;

    var user UserOne= ToUser(Trim(Part($AllUsers,1,";")));
    var user UserTwo= ToUser(Trim(Part($AllUsers,2,";")));
    ....

    ToUserList($UserOne, $UserTwo, $UserThree,
    ...)

    ------------------------------
    Anne Martin
    ------------------------------



  • 14.  RE: Formula Query touserlist error

    Posted 12-08-2022 08:38
    That formula needs to be a formula lost user type, not a user field type. Can you tell me if that fixes the problem or if nit what the error  or problem is?

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 15.  RE: Formula Query touserlist error

    Posted 12-08-2022 14:07
    Anne,

    In your solution FID 79 is an email field? 

    If so then I understand it.

    ------------------------------
    Don Larson
    ------------------------------



  • 16.  RE: Formula Query touserlist error

    Posted 12-08-2022 14:17
    Yes, it is.

    ------------------------------
    Anne Martin
    ------------------------------



  • 17.  RE: Formula Query touserlist error

    Posted 12-07-2022 18:26
    From the genius of @Kristoffer Keene


    TO get userID from pipeline 

    {{ a.user_list | map(attribute="email") | join(";") }}​


    ​​

    ------------------------------
    Prashant Maheshwari
    ------------------------------