Power Platform Community Forum Thread Details

SharePoint List Lookup Column - Editing data in a Lookup Column Cell

A SharePoint List (SPL) called Contractors with the following columns:
ContractorID // Display name of the default ID column
ContractorName

Another SPL called Reports with the following columns:
ReportID // Display name of the default ID column
ReportNumber
ContractorID // Lookup column referring to the ContractorID column of the Contractors SPL

// Additional field(column) included when creating the Lookup column Contractor_ID

ContractorID:ContractorName

List View Editing of a Record

Editing the Report SPL in List View (a simple form of each editable field displays on the right):

I see the following for the ContractorID field:

Note:

Double clicking a record will open the "edit form" and show the following:

If data already exists, see below, otherwise it shows "---" (or something like this)

ContractorID

[object Object]

Clicking "Edit" in the menu above the SP List shows a similar form, however, if there is data, that

data is displayed (say, an ID number), if no data exists it displays: "Select an option"

With some subtle nuance differences on which way I edit...

When I click in the field to edit, a listbox displays showing the referenced (looked-up) ContractorID's

from the Contractor SP List below the textbox which has: "Select an option"

I can click a choice in the listbox as usual.

But, because the listbox items are ID's I don't know which Contractor I'm selecting.

Editing the ContractorID:ContractorName lookup field(column) is not an option nor seen in the Edit Form.

Grid View Editing of a Record

If I use "Edit in grid view" I can edit the cell directly in the grid.

ContractorID cell is editable similar to list view, with a listbox appearing, containing the IDs from the Contractors SP List.

ContractorID:ContractorName lookup field is seen but this message pops up when clicking the cell:

"This cell is read-only"

When a ContractorID is selected from the listbox the ContractorID:ContractorName lookup updates automatically.

Grid view edits are quirky, but work well enough.

But still, because the listbox items are ID's I don't know which Contractor I'm selecting.

Question:

For the purposes of editing SPL records using the standard interface as described above....

Can some kind of column formatting (JSON) or setting allow the listbox to reference the readable Contractor Name, while storing the Contractor ID?

My experience is from decades in the database desktop development world and tables with lookup fields are an important tool for storing short/succinct "foreign key' primary ID's from the Lookup table, while having the ability to show the Admin/User the readable field without storing the readable data, which could be very lengthy and take up much space in all tables that reference the Lookup table.
Storing an ID while displaying and allowing the user to update the record field (via a combo/dropdown)
from seeing the readable lookup choices is very useful for maintaining data integrity.

Thanks in Advance

Categories:

Power Platform Build Tools

Power Apps component framework

Quick Links

Leaderboard > Power Apps - Power Apps Pro Dev & ISV