About entity\sumRelated issue

[alpkrgl]

Hi,

I am using this crm and i love so much this. I can do everything with this. But i can not do something with it.

I could not buy any extensions (reports pack..). I will buy. But now i want to do without it.

Thera are two entities on my crm. Opportunity and Muhasebe. There is many to many releationship.

ToplamBorc is a field in Muhasebe entity. I want to calculate all amounts with accounts on Opportunity entitiy and want to see it in Muhasebe Entity.

So, i use this formula on Muhasebe Entity "toplamBorc = entity\sumRelated('opportunities', 'amount', 'accountId', entity\attribute('accountId');". I want it to calculate amounts but not all opportunities amounts. I want to see account's amount. ScreenShots is here.

I have read all of thins about sumrelated here but i can not do. Thanks for everything.

[rabii]

hey,

what do you mean by this ?

I want to calculate all amounts with accounts on Opportunity entity and want to see it in Muhasebe Entity.

You are using the formula wrong, here is how you can use it:

Code below will sum all amounts of the opportunities related to the current Muhasebe entity

toplamBorc = entity\sumRelated('opportunities', 'amount'); 

If you want to apply filter you can use it as below, in this case only sum amount of Won opportunities.

toplamBorc = entity\sumRelated('opportunities', 'amount', 'won'); 

I hope this helps

[alpkrgl]

hey,

what do you mean by this ?



You are using the formula wrong, here is how you can use it:

Code below will sum all amounts of the opportunities related to the current Muhasebe entity

toplamBorc = entity\sumRelated('opportunities', 'amount'); 

If you want to apply filter you can use it as below, in this case only sum amount of Won opportunities.

toplamBorc = entity\sumRelated('opportunities', 'amount', 'won'); 

I hope this helps

Hi, thanks for your reply.

I want to calculate Account's amount on my Muhasebe entity. "toplamBorc = entity\sumRelated('opportunities', 'amount', 'won');" This is worrking but i don't want to use 'won' filter. I want to see Account's total amount

[rabii]

what do you mean by account's total amount ? do you have a field on Account entity called (Amount) ? if yes what is the relationship between Muhasebe and account ?

[alpkrgl]

Opportunity and Muhasebe are my entities.
There are a lot of Company Accounts' sales on Opportunity. For example X Company's sales amount is 1000$ and Y Company's is 2000$. I want Muhasebe Entity to show me how many amount are There with X Company or with Y on list. Or, how can i see X's total amount on Muhasebe Entity?

There is many to many releationship between two entities.

Thank you.

[rabii]

There is many to many releationship between two entities. (BETWEEN WHICH ENTITIES)
Also i asked where do you have amount is it on company entity or opportunity ? i am still unable to understand what you want to achieve.

[alpkrgl]

There is many to many releationship between two entities. (BETWEEN WHICH ENTITIES)
Also i asked where do you have amount is it on company entity or opportunity ? i am still unable to understand what you want to achieve.

Hi.

Many to many is between opportunities and Muhasebe. Amount is Opportunuties's field. I want to see the total amounts (from opportunities entry) of all companies in the list on the Muhasebe Entity.

[rabii]

of all companies in the list on the Muhasebe Entity. ? is there a list of accounts (companies) on the Muhasebe Entity ? If yes what is the relationship between Muhasebe Entity and Account / Company Entity?

[alpkrgl]

Hi,

I'm sorry my English is bad. In Muhasebe, only the companies I added are available. I just want to see the list of companies I added there. Screenshots are as follows. I'm sorry to bother you

Thanks

[alpkrgl]

Also I am currently using the following formula. My brother wrote this formula, but I don't want to use it because it is quite complicated and useless.

$opportunityIds = record\findMany('Opportunity', 999999999999999999, 'createdAt', 'asc', 'siparisdurumu', 'Teslimat Tamamlandı', 'accountId', entity\attribute('accountId'));

$toplamsatis = 0;
$i = 0;
while(
$i < array\length($opportunityIds),
(
$value = object\get(record\fetch('Opportunity', array\at($opportunityIds, $i)), 'amount');
$toplamsatis = $toplamsatis + $value;
$i = $i + 1;
)
);

output\printLine($toplamsatis);

toplamBorc = $toplamsatis;

[rabii]

Well the formula you have is exactly what you should use, here is a similar version using latest while and if statement

if (accountId & entity\countRelated('opportunities') > 0) {
    
    $limit = entity\countRelated('opportunities');
    
    $opportunitiesIds = record\findMany('Opportunity', $limit, null, null, 'accountId', accountId);
    
    $total = 0;
    
    $i = 0;
    
    while ($i < $ids) {
        
        $o = record\fetch('Opportunity', array\at($opportunitiesIds, $i));
        
        $amount = object\get($o, 'amount');
        
        $total = $total + $amount;
        
        $i = $i + 1;
    }
    
    toplamBorc = $total;
}​ 

[alpkrgl]

Well the formula you have is exactly what you should use, here is a similar version using latest while and if statement

if (accountId & entity\countRelated('opportunities') > 0) {

$limit = entity\countRelated('opportunities');

$opportunitiesIds = record\findMany('Opportunity', $limit, null, null, 'accountId', accountId);

$total = 0;

$i = 0;

while ($i < $ids) {

$o = record\fetch('Opportunity', array\at($opportunitiesIds, $i));

$amount = object\get($o, 'amount');

$total = $total + $amount;

$i = $i + 1;
}

toplamBorc = $total;
}​ 

Thank you very much. So can I use this as a single line like this? For example: toplamBorc = entity\sumRelated('opportunities', 'amount', 'accountId', entity\attribute('accountId')
If no, how can I use it to work dynamically? This way I have to constantly press the recalculate formulas button.

[alpkrgl]

Once this is done I will need to see how the subtotal is done on the listing screen. I may have to take up a little more of your time

[rabii]

> This will not work toplamBorc = entity\sumRelated('opportunities', 'amount', 'accountId', entity\attribute('accountId'));

as per documentation in order for this to work you need to provide a filter name like ('open') means all opportunities that are still in progress so (open) is a filter on the opportunity.

If you want to make this dynamic then you can create a custom cron job which will be executed at a specific time and will calculate the amount for all Muhasebe entities. read this https://docs.espocrm.com/development/scheduled-job/

Otherwise if you have advanced Pack (which i advise to buy) then you can use a scheduled workflow to do the job without the need for any custom code.

Advanced Pack will make your life easier, it is a very powerful extension.

[alpkrgl]

> This will not work toplamBorc = entity\sumRelated('opportunities', 'amount', 'accountId', entity\attribute('accountId'));

as per documentation in order for this to work you need to provide a filter name like ('open') means all opportunities that are still in progress so (open) is a filter on the opportunity.

If you want to make this dynamic then you can create a custom cron job which will be executed at a specific time and will calculate the amount for all Muhasebe entities. read this https://docs.espocrm.com/development/scheduled-job/

Otherwise if you have advanced Pack (which i advise to buy) then you can use a scheduled workflow to do the job without the need for any custom code.

Advanced Pack will make your life easier, it is a very powerful extension.

Thank you very much. I realy want to buy Advanced Pack. I know i can do everything with it. But, now i can not buy. One question too brothero these formulas run automatically in the background of the php file? Thank you for everything.

[rabii]

> these formulas run automatically in the background of the php file?

Formula script runs every time a record is updated. Not sure what you mean by your question though what do you run in the background of the php files ?

[alpkrgl]

> these formulas run automatically in the background of the php file?

Formula script runs every time a record is updated. Not sure what you mean by your question though what do you run in the background of the php files ?

Since I cannot make automatic recalculate formula with Cronjob, I wanted to find out if it would work if we wrote these formulas to php files.

So how can I do this recalculate formula with cronjob? Can you help me with this?