Best way to record multiple client details for one record in SharePoint list

[EmmaVR]

Hi All

I developed a list for a customer to track their logistics projects. I have 4 separate Single Text columns for them to record their client contact details, 1. Contacts Name, 2. Contacts Position, 3. Contacts Telephone number and 4. Contacts Email Address.

But the problem is that not all their projects will only have a single client contact, one project could have 5 but another could have 1 for example. They want to be able to capture as many clients as they would like to for a single record in the list.

The options that I can think of to accommodate this requirement are:

1. Make all 4 columns above into Multiple Text columns and then they can write a new client on a new line- this looks messy and doesn't exactly link each contacts details in the 4 columns up nicely alongside each other

2. Add additional fields/columns to duplicate the 4 needed columns to capture a contacts details- this makes the new data capture form and list width very long 

3. Find out a way to perhaps, if possible, allow them to capture client contact details in an excel-like table in the 'Contact's Details' field in the list, this way they can capture as many as they like for one record in the list. Maybe there's a way to add an attachment they can open in that field to fill out a datatable?

I have tried searching the internet for the best way to achieve this but have come up short.

Any guidance would be appreciated.

Many thanks

[#2]

Hi @EmmaVR,

To record multiple client details in a single SharePoint list entry, you can use a Multi-Line Text field. This approach offers a structured way to enter and store client data within a single list item.

Steps:

1. Create a new Multi-Line Text field in your SharePoint list and name it "Client Details."
2. In the field description, provide clear instructions on how to enter client details in a structured format. For example, suggest that users enter each client's information in rows with columns for name, position, phone number, and email.
3. To display the data in a more readable format, you can use JSON formatting. To do this, follow these steps:
   • Navigate to the List tab and click Settings.
   • Under Columns, click the name of the Multi-Line Text field.
   • In the Column formatting section, select JSON.
   • In the JSON formatting dialog box, enter the following JSON code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v1/column-formatting.schema.json",
  "elmType": "table",
  "headers": [
    { "title": "Name" },
    { "title": "Position" },
    { "title": "Phone Number" },
    { "title": "Email Address" }
  ]
}

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#3]

Hi @LeonPavesic 

Thank you for your response, much appreciated!

I'm wondering, do you have an image of what this would look like?

I have followed your steps and captured some example data but nothing is showing in the list in that column. 

Do I need to change the type of text as per below?:

[#4]

Hi @LeonPavesic 

I see when I open the column to format it, I get this message below:

I assume this is the reason my column is not displaying the data when I view the list correct?

[#5]

@EmmaVR table is not a valid elmType in SharePoint column formatting. https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/formatting-syntax-referen...

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

[#6]

Thanks Rob
Within the valid elmTypes, are there any that would allow me to achieve my goal?

[#7]

@EmmaVR I do lots of things with JSON but I wouldn't use it for this. I'd normally look to have a separate list for the contacts and then use Power Apps to create a nice-looking front end. But if you're not too experienced with Power Apps then you could still have a second list for the contacts but then in your main list use a lookup column to bring back the contact name(s) and any of the other details. I'm out at lunch at the moment but will show you what this all looks like when I get back.

Rob
Los Gallardos
Principal Consultant, SharePoint and Power Platform (and classic 1967 Morris Traveller driver)

[#8]

@EmmaVR so in the example below there is a second list for the contacts. I have renamed the Title column to Company but when you create the lookup column it will still refer to Title which is the internal name of the column. The columns are all single line of text columns.

Then in your main list create a lookup column and select the source as your contacts list. Select the column you want to display when you do do the lookup.

Select the columns from the lookup contact names list that you want to be included in your main list. You don't have to include all of them, just the ones that will be useful in your main list. Make sure you select allow multiple selections so that you can have multiple contacts for every item.

The column I selected above to display was Name. When you edit an item or create a new item in your main list the Contacts column will display all the names and because you selected multiple items you can select multiple names.

Selecting the contacts will then put the other details into the relevant columns.

So as well as your project columns you will end up with Contacts column plus the columns you selected earlier.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

[#9]

Many thanks Rob for your detailed response, it all makes sense!
May I take it a possible step further and check to see if JSON could then format these columns to put each contacts details on a new line, within the same line item? Would something like this be possible?

[#10]

@EmmaVR yes this can be done adding the following JSON formatting to each of the additional columns in advanced mode.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "div",
      "style": {
        "white-space": "wrap",
        "width": "100%"
      },
      "txtContent": "=replaceAll(@currentField,';','\n')"
    }
  ]
}

which gives the following result:

For the main Contacts lookup column you can use the following JSON:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "children": [
    {
      "elmType": "div",
      "style": {
        "white-space": "wrap",
        "width": "100%"
      },
      "txtContent": "=replaceAll(@currentField.lookupValue,';','\n')"
    }
  ]
}

Hope that helps.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

[#11]

@Rob_Elliott

Thank you for taking the time out of your day to assist me with this query! Much appreciated.