Jump to content
  • 0

Looping through records to get a total.


PaulBI
 Share

Question

Good day,

I have two tables. The first table (a02_Opportunities_Full_Details) captures the sales opportunity and the necessary fields. Three of the fields are sales credits for consultants, so for each opportunity I can allocate sales credits to 3 different people.

The second table (a03_Opportunity_sales_credits) is populated through a triggered action, which breaks the opportunity into three separate records, one for each consultant. I did this because across the different opportunities I want to aggregate the number of sales credits per consultant, but I couldn’t find a way to do it in a single record.

Using the second table and trigger event, I create 3 separate records so getting the individual consultant’s sales credits is possible. Please see attached image.

Please can I ask for your assistance.

Do you know if there is a more efficient way to achieve the result I need for the sales credits?

Many thanks

Paul373188376_Salescreditspng.thumb.png.3a1a0b00b7f92b70568c9fe20b23699b.png

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Hi Paul!

I would suggest redoing current table structure, because it consists of multiple duplicate information, empty fields and is not scalable in the long run (e.g. imagine you have 7 agents working on one opportunity and only 1 agent working on another).

Check out database normalization concept and table relationship.


This is a classic example of a so-called many-to-many relationship between tables.
One opportunity can have multiple sales agents working on it, and at the same time, each sales agent can be involved in multiple opportunities.

Many-to-many tables relationship can be created by combination of two one-to-many relationships.
So to facilitate such a scenario, first, we 3 tables.

1. Opportunities:
https://monosnap.com/direct/gwRKXZ89cdecqn4lMHRqySSgkxvA3Z
https://monosnap.com/direct/njA5h0UADjPaapSOZvlmVK90s2WTgR

2. Consultants/agents:
https://monosnap.com/direct/GZl8e13C5rkL39WKq9Tthim9WRYZbP
https://monosnap.com/direct/P14lLrxABHpJzTQ9TTZ55Ih45DIeW3

3. Combined table Consultants-Opportunities:
https://monosnap.com/direct/Vq93PRBPEVqbNT3nVcDg9bTQ03Vo8j

Then, define relationship relationship between tables:
https://monosnap.com/direct/Cpd3NQduE5XyDyG13POw2peq6CaBQw

Opportunities to Consultants-Opportunities is a one-to-many relationship
Consultants to Consultants-Opportunities is a one-to-many relationship

After that, you can create an input form based on the Consultants-Opportunities table to enter agent's credits per each opportunity.

And finally, you can create a pivot table report based on Consultants-Opportunities:
https://monosnap.com/direct/qI9SdRhm6th6fyzDGImpVwyDKC0Viv


1397722697_Caspio-Preview-Consultants_OpportunitiesPivotTableReport2022-04-1100-22-55.thumb.png.8a31091a352a0663a511362526b26937.png
Hope this helps

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...