Single Lookup Field with Multiple Forms Relation

Single Lookup Field with Multiple Forms Relation

Hello,

I am trying to create a new proposals tracking & sales incentives calculation application for my small-insurance business where sales persons sell different types of products (Life Insurance, Health Insurance, Motor Insurance etc.) and earn incentives based on the product type. 

What I have done until now :-

I have created three different forms for capturing the proposals - Add Life Insurance Proposal, Add Health Insurance Proposal, Add Motor Insurance Proposal.....etc. This captures all the business that happens. Correspondingly, I have created 3 Forms - Life Insurance Incentive, Health Insurance Incentive & Motor Insurance Incentive. Whenever a proposal is added in any of them and the policy is issued, based on certain rules , a record is created in the incentive forms. So if a life insurance policy is issued , a corresponding record will be created in life insurance incentive form. Similarly it happens the same way for other products too. So as soon as policy is issued, an incentive record will be created for approval and payment. And in the incentive forms, a lookup field is there which shows the original policy/transaction. So the user can also see the details of the transaction for which the incentive is being shown. 

But, this approach doesn't give me a single view of all the incentives for a particular Sales Person. I have to see three different reports for a particular sales person. This isn't so bad of an approach and i can live with this. So for a particular sales person, I can get to know how much incentive is due in life insurance , how much in health insurance..and so on. This isn't so bad of an approach and i can live with this. 

Is this how it should be done? Is the approach correct? Please tell me a better way on how this can be achieved. 

What I think might be a better way:-

Last night I thought maybe i can improve upon this approach. Maybe I should create a single form - this will capture only the - Sales Person Name, Incentive rate & amount, incentive status and product type. Then I won't need 3 different incentive forms and all the incentive records will be created in a single form. But there will still be 3 different forms for capturing the proposals i.e. Life, Health & Motor (there might be more also). So in the Single incentive form/report , how do i create a lookup to any of proposal capturing forms? So, if the incentive product type is "Life Insurance", it should lookup to the issued policy in "Life Insurance Proposals". Similarly, if the incentive product type is "Health Insurance", it should lookup to the issued policy in "Health insurance Proposal". So the requirement is to achieve a dynamic lookup of Policy No. (Or Transaction No.) to any of the proposals form based on the incentive product type.

How can this design be achieved? What changes I should be doing ? Should I use :-

a.) 3 Different proposals capturing forms -> 3 different incentive forms -> And maybe some kind of way to display it all together in a pivot report ?

b.) 3 Different proposals capturing forms -> Single Incentive Data Capturing Forms -> But then how do i achieve the dynamic lookup to the issued policy ?

c.) Maybe a combination of a.) & b.)

Thanks for your patience for reading. I'm open to all suggestions and expert opinions on this. 

Sincerely,
Tushar Arya, India






                            Zoho Desk Resources

                            • Desk Community Learning Series


                            • Digest


                            • Functions


                            • Meetups


                            • Kbase


                            • Resources


                            • Glossary


                            • Desk Marketplace


                            • MVP Corner


                            • Word of the Day



                                Zoho Marketing Automation


                                        Manage your brands on social media



                                                Zoho TeamInbox Resources

                                                  Zoho DataPrep Resources



                                                    Zoho CRM Plus Resources

                                                      Zoho Books Resources


                                                        Zoho Subscriptions Resources

                                                          Zoho Projects Resources


                                                            Zoho Sprints Resources


                                                              Qntrl Resources


                                                                Zoho Creator Resources


                                                                  Zoho WorkDrive Resources



                                                                    Zoho Campaigns Resources

                                                                      Zoho CRM Resources

                                                                      • CRM Community Learning Series

                                                                        CRM Community Learning Series


                                                                      • Tips

                                                                        Tips

                                                                      • Functions

                                                                        Functions

                                                                      • Meetups

                                                                        Meetups

                                                                      • Kbase

                                                                        Kbase

                                                                      • Resources

                                                                        Resources

                                                                      • Digest

                                                                        Digest

                                                                      • CRM Marketplace

                                                                        CRM Marketplace

                                                                      • MVP Corner

                                                                        MVP Corner




                                                                            Design. Discuss. Deliver.

                                                                            Create visually engaging stories with Zoho Show.

                                                                            Get Started Now