0

I have a Customer Table containing info such as Customer # (Number), Name(Single line of text), Bankruptcy status (Yes/No), Currency (Choice).

I also have a Invoice table which has a lookup column for the Customer #, pulling in the Customer Name.

Now, the scope of the project has made it so I need to pull in the Currency column using the Customer # lookup, which seems like it would've been straight forward; however, I don't see any of the other columns besides the system generated ones (Version, Title, ID, Created, etc.)

How can I get the lookup column to also include the currency in the Invoice table?

1

Extending Choice column using lookup columns is not supported in SharePoint.

Supported Column Types:

  • Single line of text
  • Number
  • Date and Time

Unsupported Column Types:

  • Multiple lines of text
  • Currency
  • Person or Group
  • Calculated
  • Hyperlink or Picture
  • Custom Columns
  • Yes/No
  • Choice
  • Lookup

Microsoft official documentation: Create list relationships by using unique and lookup columns

1

Okay, I solved this problem and will try my best to document on how I did it and what I needed to consider.

First, I changed the Currency to type: Single Line of Text

Second, I went into my PowerApp and for each display cards (in Galleries) showing this I removed the .Value from the end of its references, allowing the display field to properly pull the existing text.

Next, for all Forms I removed the field entirely (as I've had issues where PowerApps maintains some back-end data-type designations that create issues).

Then, I re-added it and simply changed the Control Type to a Allowed Values, and made in the Allowed Values section overwrote Parent.Default with [ "Option1", "Option2", etc...] (for syntax).

Lastly, I went to the Invoice List and under the Customer # Lookup column I selected the additional Currency column to be pulled into the table

2
  • Hi Mark S. Thanks or your enthusiastic sharing, this is indeed a good workaround. You can accept your answer as answer. The behavior will help others who meet the similar issue in the future quickly find the correct answer. Jul 29 at 9:53
  • @EchoDu_MSFT I'm happy to help support a communal learning environment and am happy to report back with my findings if it helps others. I'm just waiting for the 24 hr cooldown from when I answer to when I can accept it. 7 hours to go.
    – Mark S.
    Jul 29 at 12:10
0

Extending Choice column using lookup columns is not supported in SharePoint. However, you can try to the below workaround:

1.On the Customer list, set "Customer #" as Singel line of text column

enter image description here

2.On the Invoice list, create the same "Currency"(Choice) field as the Customer list

enter image description here

3.Create a Microsoft Flow

  • a.Go to the Invoice list, click on Integrate >> Power Automate >> Create a flow

enter image description here

  • b.flow

enter image description here

  • c.Save and Test it

4.When you create a new item in the Invoice list, wait a few seconds and refresh the page, you will find that the "Currency" field will be filled in automatically.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.