Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Confront Data from dif...
Finance forum
Unanswered

Confront Data from different Tables

Posted on by 71
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!
  • Community member Profile Picture
    Community member 11 on at
    Confront Data from different Tables
  • Community member Profile Picture
    Community member 11 on at
    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 Profile Picture
    Martin Dráb 228,135 Most Valuable Professional on at
    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 Calavon Profile Picture
    André Arnaud de Cal... 290,277 Super User 2024 Season 2 on at
    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 Profile Picture
    FabFab97 71 on at
    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 Profile Picture
    Martin Dráb 228,135 Most Valuable Professional on at
    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 Profile Picture
    FabFab97 71 on at
    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 Profile Picture
    FabFab97 71 on at
    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 Profile Picture
    Martin Dráb 228,135 Most Valuable Professional on at
    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 Profile Picture
    FabFab97 71 on at
    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? 

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,277 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,135 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans