Crow Canyon Software Forum

Forum Navigation
Please or Register to create posts and topics.

Custom Multiple Record External Data Question

We are using Custom API's to return On Premise data based on a Filter defined by a SharePoint Field.  For this example I am using a custom API to return Invoices over the last 14 days.  The query I use must be GetInvoices:CustomerNumber.  The query parameter maps CustomerNumber to a SharePoint field [Fargo Customer Number|Fargo_x0020_Customer_x0020_Numbe] and then uses that to return both InvoiceNumber and ShipDate fields for the last 14 days.

The problem is the multiple record selection field I mapped it to only shows the invoice number and not the Ship Date.  In the Query portion I've tried a SELECT clause to select both InvoiceNumber and ShipDate, but that doesn't work against the custom API.  The Database to SharePoint Column Mapping only shows two mappings, one for the Database column I am mapping, the other for the ID Column.  It doesn't appear that I can map the Ship Date column in addition to Invoice Number, it can only access the one (Invoice Number) and an ID column. (This makes sense because it is multiple selection - the logic breaks when you have two multiple selection columns I am guessing, otherwise not sure why Single records can write to multiple mappings but not Multiple Records)

I am trying to find a way to return both ShipDate and CustomerNumber in the selection field but am unfamiliar with how to get this to work in the select query as I have only been able to get GetInvoices:CustomerNumber to work here against our custom API.  I have thought maybe a Form Event action to write all returned values to multiple list items on a separate list and create a look up to view them on my form, but I haven't figured out a way to make that work either.

Uploaded files:
  • api-invoices.PNG
  • visual-of-issue.png

An update.

When I map the ID column to the ShipDate column and then enable search, I can see the ship dates under ID and the Invoice Number under value.  But when I disable search I can only see the Invoice Number.  If we could see what we see with Search during the selection without search enabled that would solve the issue.

Uploaded files:
  • Search.PNG

We can create a new column in the list and then copy 'ShipDate' column value to it. We can configure a form event action on column change for the external data column, configure an update form control value action to copy value of 'ShipDate' to the new column.

How can we see ShipDate during the selection process without activating search?  The idea is to give them a way to differentiate the invoice numbers by seeing when the invoice was shipped while they are selecting.

Writing ship date to another column gives them visibility of the Ship Date after the selection is made, my goal is to give them visibility while the selection is being made.