Discussions

Expand all | Collapse all

Formula to select user in multiple fields

cowannbell cowannbell01-09-2019 18:52

cowannbell cowannbell01-10-2019 13:22

QuickBaseCoach Dev./Training01-10-2019 13:24

cowannbell cowannbell01-10-2019 15:17

  • 1.  Formula to select user in multiple fields

    Posted 01-09-2019 17:18
    I have three user fields in my database.  Assigned to, transferred to or reassigned to.  In a report I would like to present the user with only one selection for user and it would pull records for that user if they are in any of the user fields.  Not sure how the formula should look.

    Thanks in advanced.


  • 2.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 17:30
    Are you simply looking for a report filter to only include records if the current user is the user in any of those 3 fields?



  • 3.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 17:47
    Created a new formula user field.

    if ( [Assigned to]=User() OR  [transferred to]=User() OR [reassigned to]=User() ,User() ,null )

    Filter your report to show records where the user in this new field is the current user.


  • 4.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 18:05
    I want the user to be able to select one of the users.  These reports are ran by management so current user doesn't work.


  • 5.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 18:10
    Are you looking to run a report and have the user answer as question as to which user they want and then return a report for just records where that user in in any of the three fields?


  • 6.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 18:52
    Yes.


  • 7.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 18:58
    Ok, so now we understand the question.


    I suggest making a field like this

    LIst(",",
    UsertoName([Assigned to]),
    UsertoName([transferred to]),
    UsertoName([reassigned to]))

    Then have the report have a filter where that field Contains <ask the user>







  • 8.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 19:09
    It looks like that turns into a field where they would have to key in the reps name.  Maybe I don't have the correct formula field.  I am using formula-user.


  • 9.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 19:15
    I'm not sure this is what I'm looking for anyways.  Doesn't this just bring back the list of users from these fields.  I'm thinking and I could be wrong that maybe I need a custom formula field on the report.  One that says representative is in one of these fields and representative would be a list of the users.


  • 10.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 19:40
    yes. they would need to type in a portion of the name. It will not be a drop down list


  • 11.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 20:23
    That won't work.  They are too picky for that.  Any other ideas?


  • 12.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 20:35
    The other method is more complicated.  it is possible to create a table called Select Resource.  Create 1 record.  It will be [Record ID#] = 1.

    Create a drop down field of users there.

    Then create a relationship where that Table has many detail records.  Make the link to the relationship field be a formula of 1.  Lookup the selected user.

    use that user in the filter for the report.

    use that report as the report for the report link on the form to Select user.


  • 13.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 21:55
    When you say make the link to the relationship a formula of 1.  Can you give a little more detail.  Not sure about that part.


  • 14.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 22:00
    make a relationship to the detail table and change the  field for [Related Select user] to be a formula field with a formula of just a 1.

    It will calculate to be a 1,   and the select record will only ever have one record in that table so it will be {Record ID#] = 1.

    You have to prevent all users from adding more records to that table after you create the first record.,

    There is another solution which will allow multiple concurrent users to be selecting User and not stepping on each others toes, but that is more complicated to explain.


  • 15.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 22:04
    So it would be a numeric formula field and you add just the number one in the formula?


  • 16.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 22:07
    Ok, I actually have that part working.  I think my issue is the user table.  In the user table I added one field called user and it is a user field.  I think my problem is having a list of users in that one record.  Not sure how I'm suppose to do that.


  • 17.  RE: Formula to select user in multiple fields

    Posted 01-09-2019 22:38
    When you assign users to records in those three fields, where does the drop down list come from.  Whatever that is, then this would be done the same way.


  • 18.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 13:16
    It's a user field, just like I have. I must have something set up incorrectly then.  The only option when I use that field in the report is User #1


  • 19.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 13:22
    I got it now.  I was using the wrong field in the report.  


  • 20.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 13:23
    Did you manage to get an acceptable Record Picker in the Select Record.
    Did you manage to get that selected user across to your details table?
    Did you then manage to get that selected user used in a report filter?
    Did you put that report in the select form as the report link report?

    How far did you get in the steps above?


  • 21.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 13:24
    So it�s all working now??


  • 22.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 13:36
    I got the field to working but it's not working in the report like I want.  I'm also getting numbers in the user drop down list.  So like place holders. The user field is the lookup field.




  • 23.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 13:44
    1. Remove the line which has the ask the user question.

    2. Move that date close equal blank up so it�s not part of those indented groups.

    3. Delete the last 2 indented groups.

    For the remaining indented group, use

    ANY
    Assigned to To is equal to the value in the field user User
    Transferred to is equal to the value in the field user
    Reassigned To is equal to the value in the field user user .


  • 24.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 13:47
    Where will the person running the report be able to select which user that want to run the report for?


  • 25.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 14:31
    The person will not "run the report".  The person will select a user in that 1 record select table, and the report results will be listed directly on that record as a report link field with report itself being display on the form.

    So whoever has access to that select user table will be able to see the results.



  • 26.  RE: Formula to select user in multiple fields

    Posted 01-10-2019 15:17
    Oh.


  • 27.  RE: Formula to select user in multiple fields

    Posted 01-11-2019 14:10
    Hum. I'm not sure this will work for me.  I would need to use this field in a few other reports also.  Any other suggestions or am I understanding this incorrectly?


  • 28.  RE: Formula to select user in multiple fields

    Posted 01-11-2019 14:53
    I suggest that you get working the way I suggested and then go on you your next question.  ie, don't give up just yet.

    If you like you can contact me directly for one on one assistance.

    QuickBaseCoach.com