How to prefill data on a subform properly?

Hi everyone,

I'm struggling on how to prefill the data on a subform properly.

Scenario:

I have 3 Forms: Shops, Clients and Needs Analysis.

Shops is a subform of Clients as well as Needs Analysis.

Whenever I create a new Client then I can add in the Shop subform the related Shops.

This is working.

When I start a new Needs Analysis form I do have there a lookup field for the client. Once the client is selected then the Shops subform stays empty.

What I tried was to create a workflow for the Needs Analysis form that is called when the Client in the Needs Analysis form gets changed. This is now checking all the Shops records and in case it finds a record with matching id in regards to the client it is inserted into the subform.

The related code I use is:

if(Client == Null) // the lookup field in the Needs Analysis is empty

{

input.Shops.clear(); // clear the subform Shops within Needs Analysis

}

else

{

for each row in Shops //check for each record in Shops table

{

if(row.Klienten == Client) //if the Client IDs do match then

{

row1 = Bedarfsanalyse.Shops(); //define a temp variable to fetch the data

row1.Shop_Name=row.Shop_Name;

row1.Payment_Provider=row.Payment_Provider;

row1.Url=row.Url;

row1.Shop_System=row.Shop_System;

input.Shops.insert(row); // insert the parameters in the Shops subform within the Needs Analysis form

}

Basically this is working and showing me the proper data in the subform but when I click on save the system complains as the Shop_Name already exists in the Shops table (must be a unique record).

Can you please advise how I can only show existing records or in case of saving the form make sure that exisiting records are ignored and only new Shop entries are stored?

Or maybe I'm doing completely wrong and there is a much easier way?

Just to summarize: When selecting a Client in Needs Analysis (connected via Lookup) then the subform should show the shop from this client only. Besides that I want to be able to add a new Shop in the subform that is linked to this client and I need to be able to save the new record in the Needs Analysis form.

Thanks for your support,

Lars