add recId field to entity

(3) Share

Report

Posted on by ..

1,987

Hi,

I created a table with the following fields (Table1)

Number Id codeX codeY Field1
------ --- ------- ------- --------
1 id1 codex1 codey1 fieldz
2 id2 codex1 codey1 fieldz

index "Idx" with allow duplicate No is "Number"
replacement key index is "Idx"

I created a field group with CodeX only to use it in the form

I added Table1RecId field to 'SalesLine' table where the relation is: Table1.RecId == SalesLine.Table1RecId
This field appears in "SalesTable" form with replacement fieldGroup having CodeX only

Now i want to be able to import CodeX via SalesOrderLineV2Entity

i added table1 as a read data source to the entity (outer join salesLine). I added CodeX to the entity fields and the staging table.
I also added Table1RecId from SalesLine to the entity fields, and made it private.

Now when i import, i'm getting this error:
"Matching record for the read only data source 'Table1' does not exist"

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (19)

Answers (0)

Martin Dráb 239,031 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Let's forget the error for now and rather look at whether your design makes sense at all.

You have just CodeX field in the entity and now you want to find a record in Table1 based on it and get the RecId. But you can't; CodeX doesn't identify a single record in Table1; it can be there multiple times, therefore there are more records (and RecIds) you could use. You need either more fields (typically all fields of a natural unique key of Table1) or some decision logic (e.g. we'll use the record with lowest CreatedDateTime). You current design can't be implemented.

Was this reply helpful? Yes No
.. 1,987 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

The Id field in Table1 also exists in SalesLine.

So since the entity contains the Id from salesLine, I don't want to add the field again from Table1 and let the user fill it twice during import.

So shall I use mapEntityToDataSource in this case to help in returning the correct record?

I know this is still not enough, so how can i also say after deciding the Id and CodeX, then also get the lowest created date time?

Was this reply helpful? Yes No
Martin Dráb 239,031 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You said nothing about importing ID field before. If your original description was wrong, please give us a correct one.

If you there is a unique index for ID field of Table1, forget CodeX completelety and use Id instead. CodeX is useless, because it doesn't uniquelly identify records of Table1. But if ID isn't unique, then it solves nothing - it has exactly the same probem as CodeX.

Was this reply helpful? Yes No
.. 1,987 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

ID is not unique

So SalesLine has 'Id' field and 'Table1RecId' fields

and Table1 has those fields:
I created a table with the following fields (Table1)

Number Id codeX codeY Field1
------ --- ------- ------- --------
1 id1 codex1 codey1 fieldz
2 id2 codex1 codey1 fieldz

index "Idx" with allow duplicate No is "Number"
replacement key index is "Idx"

So now I added Table1 as a datasource to SalesOrderLineV2Entity (outer join SalesLine) (read only)
Relation between Table1 and salesLine is:
Table1.RecId == SalesLine.Table1RecId

I added CodeX from table1 to the entity and I added 'Id' from SalesLine to the entity

Now when importing the excel file, I want the other fields from Table1 to be set by code and not by adding them as actual fields to the entity.

so I want this:
Table1.Id = salesLine.Id which was provided in the excel import file

Table1.CodeX is already imported as field from the excel

Table1.CodeY = salesLine.ItemId

so:
select table1 where table1.Id == salesLine.Id

&& Table1.CodeX == field provided in the excel

&& Table1.CodeY == salesLine.ItemId

now i have the RecId

then set
SalesLine.Table1RecId == table1.RecId

and in this case, this error will go:
"Matching record for the read only data source 'Table1' does not exist"

So shall i add the logic to mapEntityDatasource method? but how to write such thing?

Was this reply helpful? Yes No
Martin Dráb 239,031 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Your design still has the same problem. You want to find a record in Table1 by CodeX, but you can't, because CodeX doesn't uniquelly identify a record in Table1. You should use Table1.Id instead.

I don't know what you mean by "I added 'Id' from SalesLine to the entity". There is no Id field on SalesLine and you also didn't mention using this field for anything.

Regarding "So shall i add the logic to mapEntityDatasource method?", you first need to define logic that makes sense. Let me repeat that you can either use a field that can uniquelly identify a record in Table1 or, if you use fields that match several Table1 records, you need some logic to select one of them. What the behavior should be isn't an implementation decision; it's depends on what business users need from the entity.

Was this reply helpful? Yes No
.. 1,987 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

Id is a custom field, i added it to SalesLine and it also exists in Table1.

I know that there are multiple options in table1, that's why i want to write code to select which record I want from table1 and assign it's recId to SalesLine.Table1RecId

I'm trying to do sth like this but i don't think it's working

Was this reply helpful? Yes No
Martin Dráb 239,031 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You can't "write code to select which record I want from table1" until you decide which record you want, which you apparently haven't.

Your current code means that you take the first record that the database returns for the given values of CodeY and CodeX, which doesn't sound like a reasonable approach to me. If you decide (based on business requirements!) that you want to take the first record, you must say which sorting you want to use. The term "first" makes no sense without an order of elements.

But maybe your current approach is completely wrong. It can't be decided without knowing the business requirements.

Was this reply helpful? Yes No
.. 1,987 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

There is another condition to the select statement which is && table1Local.IsActive == true.

I want to select first record and that's it (90% of the time, only one record will be returned from this select), if there were two records returned from this select, then i want the first one.

How to achieve this? if you want ordering then let's say order by recId asc -- that's why the business wants

Was this reply helpful? Yes No
Martin Dráb 239,031 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

All right, if you don't care which record you want, then you can use such logic.

Then you can do it in mapEntityToDataSource(). For example, you may have Table1RecId as a private entity field and assign its value in code (after finding it by a select statement).

Was this reply helpful? Yes No
.. 1,987 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

yes i already added the Table1RecId field as private.

But with the code i showed you in the screenshot, it still didn't work ( i got the same error when importing). What's wrong with the code?
is it the way i'm trying to get the SalesLine?

Was this reply helpful? Yes No