Finance | Project Operations, Human Resources, ...

Answered

Regarding temp table populate

(0) Share

Report

Posted on by waytod365

351

Hi all,

One month before i posted one post : //community.dynamics.com/365/financeandoperations/f/dynamics-365-for-finance-and-operations-forum/429370/regarding-complex-sql

Mr.martin helped but unfortunately that SQL step failed and its some sync issue came.

I skipped that steps now and i will do temp table steps.

Table name : TPL_SummaryCTC

Fields : From date and to date, amountCur.
My new temp table : TPL_SummaryCTCTmp
Fields : Jan, feb,mar,,,,,,,,,Dec, total salary.(13 fields).

below my code:

public server static void TempData(TPL_SummaryCTCTmp _tmp)
    {
        TPL_SummaryCTC summaryCTC;
        TPL_SummaryCTCTmp  tmp;
        
        delete_from tmp;

       
        select  sum(AmountCur)  from summaryCTC
            where summaryCTC.FromDate >= datestratMth(mkdate(1,1,2021)
            && summaryCTC.ToDate <= dateEndmth(mkdate(31,1,2021);
            
            
            summaryCTC.AmountCur = tmp.Jan;
            summaryCTC.insert();
            
            select  sum(AmountCur)  from summaryCTC
            where summaryCTC.FromDate >= datestratMth(mkdate(1,2,2021)
            && summaryCTC.ToDate <= dateEndmth(mkdate(28,2,2021);
            
            
            summaryCTC.AmountCur = tmp.Feb;
            summaryCTC.insert();
            
    }

As shown the code above, i want without hard code how to do datestartmonth and endMonth ? don't want current date.

2. 12 select statement need for that ? if so, how to do in12 select statement all month insert ? i did one by one insert.

Could you please give me example for that.

Thanks

I have the same question (0)

All responses (12)

Answers (4)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report
First of all, let me format your code a bit, to make it easier to read:

public server static void TempData(TPL_SummaryCTCTmp _tmp) { TPL_SummaryCTC summaryCTC; TPL_SummaryCTCTmp tmp; delete_from tmp; select sum(AmountCur) from summaryCTC where summaryCTC.FromDate >= datestratMth(mkdate(1,1,2021) && summaryCTC.ToDate <= dateEndmth(mkdate(31,1,2021); summaryCTC.AmountCur = tmp.Jan; summaryCTC.insert(); select sum(AmountCur) from summaryCTC where summaryCTC.FromDate >= datestratMth(mkdate(1,2,2021) && summaryCTC.ToDate <= dateEndmth(mkdate(28,2,2021); summaryCTC.AmountCur = tmp.Feb; summaryCTC.insert(); }

Now I see that you have typos there - I think you meant dateStartMth() instead of datestratMth(). It means that your code either doesn't even compile, or what you gave to us isn't the code that you're actually using. Could you give us correct code, please?

There are other obivous bugs, such as that your queries are completely ignored and you never populate the values you're trying to use. But there is no point in discussing it in detail if it's not your actual code.

Was this reply helpful? Yes No
Suggested answer

ergun sahin 8,826 Moderator on at

Like (1)

Report
static void MthLoop(Args _args) { TPL_SummaryCTC summaryCTC; TPL_SummaryCTCTmp tmp; StartDate firstDate = mkDate(1,1,2021); void tempData(StartDate _startdate) { StartDate startdate = _startdate; EndDate endDate = dateEndMth(startdate); if(mthOfYr(startdate) == mthOfYr(firstDate) && startdate != firstDate)//1 year loop return; select sum(AmountCur) from summaryCTC where summaryCTC.FromDate >= startdate && summaryCTC.ToDate <= endDate; tmp.AmountCur = summaryCTC.AmountCur; tmp.IntMth = mthOfYr(startdate); tmp.insert(); startdate = endDate 1; tempData(startdate); return; } delete_from tmp; tempData(firstDate); }

I preferred to use the tmp table vertically. In this way, 12 records will be created, but you will have a dynamic structure. Otherwise, you can manage which month field to write to with swtich case.

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report

Many thanks for your answer sir,

What I was expecting this dynamic structure.

Can you tell me the dynamic structure form what should i do in form? i did it below let me confirm with you.

1. I created simple list pattern.

13 fields in the grid [Jan, Feb,......Total amount]

2.As you mentioned above code , i found it [Intmth, amountCur] two fields only that IntMth field dynamically created 12 fields ?

3. Also vertically temp table step tell me sir.

4. In future If i needs to meet normal structure form I will use as you mentioned swatch case. in 12 fields once you free give me a example.

Would be great if could elaborate.

Thanks a lot beforehand

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report

5. last point As you mentioned the code where can i write and is that a tmp table new method?

And normally populate init method, becoz its seems created main method that is why i asked.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report
If you want to show 12 fields in a grid, you either need table with 12 fields or a view with 12 compulted columns (as I showed you in the previous thread). If you now don't want to use computed columns and you rather switched to a temporary table, you need 12 fields in your temporary table.

It's a pity that you didn't describe your problem and you merely said that you want an example - now we don't know which part we should demonstrate. The switch could look somehow like this:

int monthNum = mthOfYr(startDate); switch (monthNum) { case 1: tmp.Jan = summaryCTC.AmountCur; break; case 2: tmp.Feb = summaryCTC.AmountCur; break; ... }

It can be done more efficiently than running 12 database queries, but let's not make it even more complicated for you.

Was this reply helpful? Yes No
Verified answer

ergun sahin 8,826 Moderator on at

Like (1)

Report
static void MthLoop(Args _args) { TPL_SummaryCTC summaryCTC; TPL_SummaryCTCTmp tmp; StartDate firstDate = mkDate(1,1,2021); void tempData(StartDate _startdate) { StartDate startDate = _startdate; EndDate endDate = dateEndMth(startDate); if(mthOfYr(startDate) == mthOfYr(firstDate) && startDate != firstDate)//1 year loop return; select sum(AmountCur) from summaryCTC where summaryCTC.FromDate >= startDate && summaryCTC.ToDate <= endDate; tmp.AmountCur = summaryCTC.AmountCur; //tmp.IntMth = mthOfYr(startDate); //tmp.insert(); //Only one record int monthNum = mthOfYr(startDate); switch (monthNum) { case 1: tmp.Jan = summaryCTC.AmountCur; break; case 2: tmp.Feb = summaryCTC.AmountCur; break; ... } startDate = endDate 1; tempData(startDate); return; } delete_from tmp; tempData(firstDate); tmp.insert(); }

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report

Ergun sir, same method populate to init method am i correct ?

Was this reply helpful? Yes No
waytod365 351 on at

Like (0)

Report

Please can you tell me sir, step dynamic structure.

Once you tell I will note and let me ingrain this to my mind. I'm curious to know that steps.

Was this reply helpful? Yes No
ergun sahin 8,826 Moderator on at

Like (0)

Report

"same method populate to init method am i correct " ??? (My English is not very good. I'm not sure I understand correctly. I will answer as far as I understand.)

It doesn't matter where you write it. Just notice that tempData is inner method. (code uses variables in inner method)

I would put a button (populate) and use it. If your tmp table is really going to be tmp, you can change the method to return the tmp table and set it under the button.

Finally, I would add it as a static method if development had a common class. It is also acceptable to add to the tmp table.

Was this reply helpful? Yes No
Verified answer

ergun sahin 8,826 Moderator on at

Like (1)

Report

Let me explain why I said it is more dynamic. (Depends on demand, of course)

There are two different tables

One is the 12-field table you opened, and the other is my 2-field table.

There is only one record in your table and all months are kept in different fields.

Mine has 12 records, it keeps month information and amount records.

First, we saw when filling the data, we had to write switch case in your table.

Consider that we need the value of the 3rd month in the code. You know the field in your table, you can use it directly. I can select and use it for the 3rd month.

But, think a case that we don't know which month code wants, it dynamically (perhaps by date) it wants the amount of the month. I can directly select and give it. You have to write switch case.

Think about going a step further and joining with another table, I don't care what month it is. I can add it directly to join. You will need a second table that will map the table fields to the month values.

Or think we want to use it for more than 12 months, I would add a year field to table, but you need to add new fields and fix all your codes.

Or consider that the first month is July, not January (2020). Looking at your records (unless you cut the code in december). All records will appear as if they are from the same year.

.

.

.

In short, with your use of tables, you always need to teach ax which field match to which month. In mine, on the other hand, since I keep the month information, the ax already knows the matching.

Of course, according to the usage demand, your design may be more useful.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Regarding temp table populate

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Regarding temp table populate

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates