Currently the only possibility to build hierarchy in forecasts is by using connected objects from the columns of the original object in the forecast (by selecting them in the level of hierarchy directly):

As for the error message itself: a foreign key must reference a (base) table* in the same database (a base table: not a view, not a foreign data wrapper table) as stated here. In your case one of the columns is referencing a view so that's why the error message popups when updating the database structure.

Hello,

It is indeed possible to calculate such value right inside of the forecast!

If you already have the "Opportunity amount" column in the forecast, you would need to do the following.

You would first need to add the "Probability, %" column to the forecast:

Notice that we have checked the "Hide column" checkbox since we do not want this column to be shown in the forecast table. If you want to edit it later, you can just add it back by clicking View -> List of columns:

After that, you need to add your calculated column:

Now you will be able to see the result after pressing the "Calculate" button!

On the screenshot below, the opportunity in the red rectangle has a probability of 50%, therefore the value in the calculated column is half of the real one:

Hope it helps!

Best regards,

Max.

Thank you for your clear reply. I thought of this, but it does not seem to work, as I want all those opportunities to be aggregated. So the calculation works on only one opportunity, but not when there are multiple opportunities, it seems because of the "sum" function on the calculation method. For example, I have 2 opportunities, assigned to 2 reps, so calculations work:

However, If I assign both opportunities to the same rep, then the % adds up to 130% and it seems to recalculate instead of just adding all the probable values:

The difference I have with your example is that I used the Contact section for my Forecast, to display sum by rep (it is a B2C use case, so accounts are irrelevant). So for that % probability, I am binding contact with opp owner, instead of doing it opp to opp like you are.

David Belanger-Goyette,

Unfortunately, the correct grouping value will be shown only if individual records are available in the forecast.

You can change the forecast to the following setup:

It will have the same structure, but will feature individual opportunities if you open contacts:

Best regards,

Max.

Thank you. I did not do it using the opp object because if the forecast runs on opportunity, then I will need to add all opportunities every time I want to check the forecast. I can add all opportunities now, but if new ones are created tomorrow, they will not show unless I manually add them. 

Hi Jonathan!

If you are struggling to find the name of your forecast, I would recommend opening the network tab in the browser console(F12) and adding an opportunity to the forecast while the tab is open.

After that, in the Network tab, you will see a corresponding BatchQuery request:

The rootSchemaName is the name of that forecast. In this case it is "UsrOpportunityForecast".

The columnValues are what columns are needed to be filled in in order to add a record.

You can then go into the configuration and find the forecast by that name:

The title is what you should look for in the business process. Here it is Forecast by "Opportunity".

Hope it helps!

Best regards,

Max.

Max,

Thank you a lot for the detailed explanation. However, look at my problem :

The network tab :

Advanced settings :

Business process :

Why can't I see the Forecast by "Opportunity" like your are able to see ?

Best regards,

Jonathan

Dear Jonathan,

Please try publishing the object.

And also check if the current package is lower in the hierarchy than the package where the UsrOpportunityForecast is located.

Best regards,

Max.

Max,

Thank you a lot for your answer.

The "UsrOpportunityForecast" was located in the custom package.

In fact, my current package is dependent of the Custom package. So the custom package is below my current package. 

This is why I could not see the object !

Many thanks for the hints ! :)

Best regards,

Jonathan

Is it possible to add new Forecast to another package, not to the Custom?

Hello Damien,

Hope you're doing well.

You can try to delete the unnecessary period records, it shouldn't affect the functionality of the system as far as it doesn't have strict bindings to the sections' functionality. Also, you can try to change the date to a future time or other actual periods for you.

Best regards,

Roman

Hello,

Actual column calculates opportunity amount.

Here is the example of created opportunity current month:

Formula and conditions for actual column calculation:

And result:

Make sure your calculation condition is satisfied so that the calculation could be performed.

Regards,

Dean

Hello!

There is no way to import data to forecasts. This feature will be available in the nearest future versions.

As the workaround, if you have some data, for example opportunities, you can try to import them and perform required calculations in forecasts based on it.

Regards,

Dean

Hi Dean,

how can data from some Creatio table (opportunities or just a custom object) can be used as a source for target values for a forecast?

Regards,

Alex

Hello,

When creating the new tab you can select the necessary source entity:

Regards,

Dean

Dear Fulgen,

Unfortunately, there is no functionality for data import of export in the forecasts section since it is based on multiple tables. Theoretically it might be possible to import the data into ForecastItemValue and ContactForecast however it is quite complicated to match all the data and map the columns correctly and we do not recommend doing that. We already had similar requests from different customers, therefore most likely this option will be available in the future application releases.

Best regards,

Dean

Thanks Dean,

What are the tables involved on this module? I am trying to insert data directly on the database.

Fulgen,

The tables that are used in Forecast module are: Forecast, ForecastColumn, ForecastCoulmnType, ForecastDimension, ForecastIndicator, ForecastItem, ForecastitemValue, ForecastRow and ForecastSheet. And as Dean mentioned - it will be very difficult to map all data between these tables and unfortunately there is no documentation on this topic. If you need to do that - you need to explore all connections between those tables and only after that you can start data import.

Best regards,

Oscar

Dear Krishna,

Hope you are doing well today.

Unfortunately, there are no any way to adjust different time periods other than that offered in the application. However we have in plans to implement the functionality for more flexible time adjustment. As for now you can only use months.  You can display the required months by adjusting the time periods http://prntscr.com/noitix  As for the forecasting values - they are stored in ContactForecast, AccountForecast and ForecastItemValue tables.

Best regards,

Dean

Dean Parrett,

Thanks Dean!! But I guess "ForecastItemValue" is made obselete from Ver 7.14. In that case in 7.14 which is the table which stores value? I tried to query "EntityInForecastCell" but got sql error "invalid object". 

Dear Krishna,

You need to use these titles - ContactForecast, AccountForecast and ForecastItemValue. These are correct names of the tables. Indeed, the query to EntityInForecastCell will error since there are no such table.

Best regards,

Dean

Dear Julius,

Hope you are doing great today.

As for now, the section Forecast works without any issues. Since you face some problems with the section, we have to look through your system settings to identify the reason for them. Therefore, I'd like to suggest you to contact our support team by email support@bpmonline.com for further investigation in your particular environments. 

Best regards,

Dean

I can only get Pipeline values Calculated on Forecasts set to Period type: Year.

Pipeline values are not calculated for any forecast with Period type: Month. These are always zero.

I'll take this info to Support

Dear Julius,

Our support team will be glad to assist you.

Have a great day ahead.

Best regards,

Dean

Dear Jess,

Unfortunately, there is no option to create a dashboard based on the [Forecasts] section because it uses tables of other sections in the Data Base. As an alternative, you can build charts on objects of [Opportunities] and [Invoices]. 

We have registered the suggestion to allow building the dashboards based on the forecasts directly. Our R&D team will consider it for the further releases. 

Lisa

Lisa Brown,

Is there any news about this topic? 

Thanks