Confront Data from different Tables

(0) Share

Report

Posted on by FabFab97

Hi everyone, I am trying to learn D365 Fo and I'm having some issues at the begininng, wich I think are very basic.
In this case I'm creating a new Journal from the Table VendInvoiceInfoLine. In this table I have a lookup field where i select the Intent Letter that receives the request, and another field with the Total Amount of the Journal. Then I have the table IntentLetter where are stored all the intent letter and they have a field called RemainingAmount. I need to compare the Total Amount of the Journal with the Remaining Amount of the intent letter, and check if the first one is lower than the second in order to proceed with the registration of the Journal. I think I need to use the While select statement to access the right data, maybe using RecId and a join, but I really don't know how to proceed and what's the right way to do it. Can anyone help? Thank you very much!

All responses (31)

Answers (0)

Community member 11 on at

Like (0)

Report

Confront Data from different Tables

Resolved with Details for issue 474182 (dynamics.com)
Community member 11 on at

Like (0)

Report

Confront Data from different Tables

>Hello,

regarding your task i think that the following setup could help you:

The above flag should trigger the following error when RemainingAmount < Total Amount of the Journal

However, I think that there is a bug that does not allow me to correctly work with that flag in other company different from ITCO and also in other environments.
Martin Dráb 230,371 Most Valuable Professional on at

Like (0)

Report
Confront Data from different Tables

If you debugged your code, you'd be able to tell us more that just showing a piece of code and saying that it doesn't work. For example, you'd be able to say:

The purchTable variable is populated with correct data, but the select for intentLetter doesn't fetch any record. I tried removing the field select:

select intentLetter where intentLetter.IntentLetterId == intentLetterId_IT && intentLetter.RecId == purchTable.RecId;

but it still doesn't fetch anything, therefore the problem is with the query conditions, but I don't see what's wrong.

When you isolate the problem, you know what to focus on and you have a better chance to solve it on your own. And if you can't, you'll be able to tell us much more that mere "this code doesn't work".

By the way, I didn't gave up because your lack knowledge, but because I told you these things several times and it didn't have any effect. If you ignore what I'm saying, it seems that you're not interested...
André Arnaud de Cal... 291,642 Super User 2024 Season 2 on at

Like (0)

Report

Confront Data from different Tables

Having two different tables with the same record ID is not weird. The Record IDs are unique per table, not per database.

I now only looked at your last posts where you shared coding. Note that you can't compare the RecIds of both tables. Also first check if the purchase order indeed has an intent letter ID filled. Otherwise there is no filter value and it can return the first record of the Intent letter table.
FabFab97 71 on at

Like (0)

Report

Confront Data from different Tables

but What informations do you need, I did debug the code, this is the only way I could get data in the debugger. Now I have the right Id of the intentLetter i need. I also posted the screen of my debug where I see that PurchTable and IntentLetter have the same RecId (or at least is what it seems to me). I getting close to the solution but I can't debug if I have an error in my code, I just don't get what join is expected to run my code. If you want me to provide more info just tell me, but simply telling to a beginner "use the debug" is not helpful.
I wanto to understand what I'm doing, so next time I'll be able to do it on my own, and id I'm asking something that looks stupid to you is not the same for me, cause I've been spending a lot of time and energy on this project .
Martin Dráb 230,371 Most Valuable Professional on at

Like (0)

Report

Confront Data from different Tables

This discussions feels like a waste of time. What you really need is using the debugger to see analyze what's going in your code. Writing code, checking whether it 100% works or not and then asking in a forum (without providing any debugging information) is not the right thing to do. Debugging is.
FabFab97 71 on at

Like (0)

Report
Confront Data from different Tables

I tried also doing like this:
purchTable = this.purchTable; if (purchTable) { str intentLetterId_IT = purchTable.IntentLetterId_IT; select intentLetter.RemainAmountMST() from intentLetter where intentLetter.IntentLetterId == intentLetterId_IT && intentLetter.RecId == purchTable.RecId; if (intentLetter) { Amount remainAmountMST = intentLetter.RemainAmountMST(); info(strFmt("RemainAmountMST: %1", remainAmountMST)); } }

but nothing changed
FabFab97 71 on at

Like (0)

Report
Confront Data from different Tables

Ok with purchTable_ds I didn't get any data, but I managed to do something with this.PurchTable. I don't know if this can be the right approach, I'm trying to have an info with the right record.
I have now access to the Id of the intentLetter I need to retrieve the RemainAMountMST() data with this code:
purchTable = this.purchTable; if (purchTable) { // Ottenere l'IntentLetterId_IT dalla riga corrente di PurchTable str intentLetterId_IT = purchTable.IntentLetterId_IT; select RemainAmountMST() from intentLetter where intentLetter.IntentLetterId == intentLetterId_IT; if (intentLetter) { Amount remainAmountMST = intentLetter.RemainAmountMST(); info(strFmt("RemainAmountMST: %1", remainAmountMST)); } }

inside the ValidateIntentLetter method. The problem is that now I get the error "Join Expected" on the select statement, and I really don't get what should I join, I tried to change it to While Select, join PurchTable but it didn't work. Once I manage to have the right RemainAmountMST on my info I think the job is done
Martin Dráb 230,371 Most Valuable Professional on at

Like (0)

Report
Confront Data from different Tables

I'm saying that if this query:
select firstOnly * from purchTable where purchTable.RecId == this.PurchTable_ds.getFirst(1).RecId;

doesn't give you any result, it must mean that you have a bug at the right side of the condition, i.e. in this.PurchTable_ds.getFirst(1).RecId. Debug this part of your code to see what it actually does.

If you want get the currently selected PurchTable record, using the existing purchTable variable (without any additional select statement) should do the job. The form has a data source called PurchTable, which automatically creates several variables. It seems you noticed purchTable_ds but not purchTable.
FabFab97 71 on at

Like (0)

Report

Confront Data from different Tables

What would you suggest to try? The fact is that now, except the RemainAmount data, everything seems to work, so I'd prefer not to start again with a new code. But if it's easier maybe I shoud. Btw with the debug I checked if I can see any data from intentLetter and PurchTable, and this is what I get:

So I can see that the RecId of the intentLetter number 3 is the same of the RecId of the PurchId number 3?