Discussions

 View Only
Expand all | Collapse all

Choices For Selection Field/Look Up Fields

  • 1.  Choices For Selection Field/Look Up Fields

    Posted 09-09-2021 10:41
      |   view attached
    I apologize in advance for how I try to explain my scenario but hopefully someone can help.

    I have 2 tables in my app with a relationship set up.  "Sites" and "Extracts" with Sites being the parent table.  When I enter a record on the Extracts table, I want field Health System to be the field I select from and 5 other fields from the Sites table to populate based on my selection.  This is working except that the Health System field is populating with a choice from a different field until I save the record.  I've attached a document showing what is happening.  I also want the selection list to show different fields in the selection box.

    I hope this makes sense and the attachment shows enough details.

    ------------------------------
    Rachel Miller
    ------------------------------

    Attachment(s)



  • 2.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-09-2021 10:48
    np,
    There are two ways to control this.  The first thing that you wanna do is go to the advanced properties for the table where these names come from and set up to three record picker fields. The record picker fields are fields that help users pick records!  That should be a housekeeping thing that you do on every single table that you create.

    But beyond that you can create any report you like and call it for example "drop down list of active Health Systems".  You can then  include any fields you like and control the sort and also have filters on that report. If you go that route then you set the form properties to use that report instead of the default record picker.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-09-2021 15:21
      |   view attached
    Thanks Mark!  Changing the record picker fields got me half way there.  My issue now is the field that I want users to pick from is now set to Entity instead of Health System Name.  I'm not sure how that changed since I only made the record picker change so far and I made Health System Name the first one.  Any idea what made it change to entity and how I can change it to the field I want?

    ------------------------------
    Rachel Miller
    ------------------------------



  • 4.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-09-2021 15:28
    I suspect this is the issue:

    If you look at the relationship where the drop down is coming from, you will see that there is a lookup field of Entity which is set to be the "Reference Proxy" in purple.  The "Reference Proxy"  field is meant to be the field that best represents the Parent Record especially when the Key field of the Parent record is meaningless to the users, such as [Record ID#].

    On that relationship the field at the top right hand side will be the field for related parent. (whatever you call Parent).  That is the field that we do not want to expose to users because it's not meaningful. So edit the field for related parent and you would likely find that the proxy field i.e. the lookup field Best represents the parent is set to entity. Edit that to be set to [Health System Name].

    Then on your form, you need to to put the field for Health System Name on the form that is the field which will allow your users to choose the correct Parent.  The entity field will be relegated to just be a normal look up field and will not populate until the health system name is selected.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-09-2021 15:46
    As always, you found the issue and gave me the solution.  I knew I had to be missing something somewhere.  Thank you for the quick replies!


    ------------------------------
    Rachel Miller
    ------------------------------



  • 6.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-14-2021 12:39
    I've run into a new issue.  I'm unable to upload data (using a spreadsheet) to the Health System Name field.  When I review the fields I am uploading, I am not able to select that field for the Import To and none of the other fields from my related table are available either.  Is there a way to fix this?  I need to add a couple thousand records so doing it manually really isn't an option.  Also, if we get the Health System Name working for an upload, will it still populate the other related fields automatically through an upload?

    ------------------------------
    Rachel Miller
    ------------------------------



  • 7.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-14-2021 13:31
    You cannot update a lookup field.  It takes its value form the relationship from the parent record. 

    You have two main choices.
    One is to dump the parent table to excel being sure to get the [Record ID#] and the Health System Name.  Then using excel formula VLOOKUP, get the record ID# of the Health system name for each record into your update and during the import map that field into [Related Parent].  That can be a practical solution if it's just a one time upload. 

    But if these uploads are going to happen all the time then the only practical method is likely to change the key field of the parent table to be the health system name.  That way when you do an upload they will either add new records if needed or they will update existing records.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-14-2021 14:21
    Uploading is going to be something we do on a fairly regular basis given the amount of entries we will have now and in the future.  I've changed the key field on my parent table to be health system name but I am still unable to select that field (or any other fields from my parent table) when trying to upload records to my child table.  I have the parent table fields included on my form for the child table and I can manually make a selection for the health system name field, it just doesn't appear as a choice for my upload.  Any other suggestions for making this work?

    ------------------------------
    Rachel Miller
    ------------------------------



  • 9.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-14-2021 15:05
    Can you post an image of the relationship?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 10.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-14-2021 16:00
      |   view attached


    ------------------------------
    Rachel Miller
    ------------------------------



  • 11.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-14-2021 16:07
    It is apparent that the Key field of Sites is not Health System name.  It is still [Record ID#].

    You may want to make a copy of your app and try changing the key field there first if you are unfamiliar with changing the key field of a parent table.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 12.  RE: Choices For Selection Field/Look Up Fields

    Posted 09-14-2021 17:53
    I made a change in the completely wrong spot - thanks for catching my error.  Making the change in the correct place fixed my issue.  Thanks again.

    ------------------------------
    Rachel Miller
    ------------------------------