Writing multiple selected values into SQL Server table

[chmoore]

Hi,

Basically there is a JobRequestID that is unique. I want to select multiple values from a defined lookup list. As there will be multiple values selected then when we save the record in Radzen we need to duplicate the rows in our SQL data table to place the multiple values on to seperate rows. I have attached the way it looks. Basically how do you write multiple lines into an SQL data table from radzen (for unique entries) to give each multiple value selected a seperate row?

On the attached example it is the TeamMemberID that has multiple values.

Thanks

Chris

[korchev]

What you are asking isn't supported out of the box for scaffolded pages and customizatons would be needed. Can you provide the schemas of the other tables:

1. The master table (that we are updating and you want to insert records in)
2. The detail table that is data-bound to the multiple select.

[chmoore]

Hi,

Please see the simple coding example below;

---Team Members Lookup---

CREATE SCHEMA Test;
GO
CREATE TABLE [Test].[Lookup_BI_TeamMembers]
( TeamMemberID INT NOT NULL PRIMARY KEY,
TeamMember VARCHAR(50) NOT NULL
)
INSERT INTO [Test].[Lookup_BI_TeamMembers] (TeamMemberID , TeamMember)
VALUES ('1', N'John Smith'),('2', N'Roy Keane'),
('3', N'James Cameron'),('4', N'Natasha Redfearn'),
('5', N'Jason Roberts'),('6', N'Natalie Jones'),
('7', N'jack Russell'),('8', N'Helen Rishton'),
('9', N'Tom Richards'),('10', N'Alex Adams')
GO

-------Master Table------------

CREATE TABLE [Test].[BI_JobRequest]
( JobRequestID INT IDENTITY(1,1) PRIMARY KEY,
RequestDescription VARCHAR(255) NOT NULL, ---FreeText
RequestDate DATE NOT NULL, ---Select from Date Picker
DeadineDate DATE NOT NULL, --Select from Date Picker
TeamMemberID INT NOT NULL
)

GO

/Create Foreign Key for TeamMemberID/
ALTER TABLE [Test].[BI_JobRequest] WITH CHECK ADD CONSTRAINT [FK_BI_JobRequest_Lookup_BI_TeamMembers] FOREIGN KEY([TeamMemberID])
REFERENCES [Test].[Lookup_BI_TeamMembers] ([TeamMemberID])
GO

ALTER TABLE [Test].[BI_JobRequest] CHECK CONSTRAINT [FK_BI_JobRequest_Lookup_BI_TeamMembers]
GO
/*********************************************************************/

My organisation is new to Radzen and there will be user requirements to be able to write multiple rows into a SQL Server table. Please see below;

Alternatively the data could even write into the main table like the below but its not ideal;

We would like to understand both ways if its possible. As I mentioned in my earlier post the single select on a filter works great and writes into our master SQL table. We would like to understand how to customise multiple selects on a filter so that we can write it back into our master table. Once we have this knowledge then we can apply this to a lot of user cases across the business and expand our use.

Thanks

Chris

[korchev]

Thank you @chmoore!

We will try to prepare a sample application based on the provided schema and describe the approach.

[korchev]

Hi again @chmoore,

I see an issue with the SQL schema and the pasted sample data. JobRequestID is configured as primary key but in your sample there are two records with the same JobRequestID (1). This won't work as the primary key must be unique.

I think you need a third table which associates TeamMembers with JobRequests and has two columns that are foreign keys to TeamMemberID and JobRequestID (the typical many-to-many relationship). Then to associated a JobRequest with multiple TeamMembers you need to add records to that table

JobDescriptionID	TeamMemberID
1	3
1	5
2	3
2	5

We have a sample project that handles a similar relationship - Order - OrderDetail (it uses a DataGrid instead of multiple select though).

Will something similar work for you? I can adapt the sample project to use a MultipleSelect.

[chmoore]

Hi,

Thanks very much for your support. I now understand the requirement to make a 'bridging table' like you have outlined above. What would be really useful to us is if you could adapt the sample project to use a MultiSelect. Is that OK? In the current sample project you have to add a car to an order one by one. Ideally we would want to add more than one car in the same visit to the order detail form. If we understand this logic then we can apply it to our business case.

Many thanks @korchev

Chris

[korchev]

I just uploaded anew version of that sample which uses a multiple select.

Here is what was needed:

1. Retrieve all products
   
   
2. Retrieve the order details that belong to the current order - invoke the getOrderDetails method and filter by OrderID equal to ${parameters.Id}.
   
   
   
   Set the $expand parameter to Product. Set the filter from the query builder
   
   
3. Handle the Then event of the getOrderDetails method and set a new page property with Name currentProducts and Value ${result.value.map(orderDetail => orderDetail.Product)}.
   
   

Now that all needed data is in retrieve it is time to add the multiple select dropdown.

1. Add a new form field with type lookup.
2. Convert the Form to TemplateForm.
3. Configure the DropDownList like this:
   
   

This will display the currentProducts page property and also update it if the user checks/unchecks items from the dropdown.

To handle the server-side updates you need to create a custom server method.

1. Add a help class that will be used to pass the parameters - the OrderID and current products.

      public class SetProductsRequest
      {
          public Product[] Products { get; set; }
          public int OrderID { get; set; }
      }
   

2. Inject the Entity Framework Context in the constructor of the ServerMethodsController.

      private readonly SampleContext context;
      public ServerMethodsController(SampleContext context)
      {
          this.context = context;
      }
   

3. Add a new method that updates the database.

       [HttpPost]
       public IActionResult UpdateProducts([FromBody]SetProductsRequest request)
       {
           var order = context.Orders
               .Where(o => o.Id == request.OrderID)
               .Include(o => o.OrderDetails)
               .FirstOrDefault();
   
           if (order != null)
           {
               try
               {
                   // First remove all OrderDetails
                   foreach (var orderDetail in order.OrderDetails)
                   {
                       context.Remove(orderDetail);
                   }
                   // Then add new ones
                   foreach (var product in request.Products)
                   {
                       var orderDetail = new OrderDetail() { OrderId = request.OrderID, ProductId = product.Id };
                       context.Add(orderDetail);
                   }
   
                   // Persist changes
                   context.SaveChanges();
   
                   return Ok();
               }
               catch (Exception ex)
               {
                   return BadRequest(new { error = new { message = ex.Message } });
               }
           }
   
           return NotFound();
       }
   

The complete code is available here.

Finally invoke that method in Radzen by handling the Submit event of the TemplateForm. The request parameter is set to { orderId: ${parameters.Id}, products: ${currentProducts} }

.

[DoubleCarpet]

@korchev

Hi, my name is James and am also trying to learn Radzen.

I have been trying to do something similar recently and tried to follow these steps using my own simple practice database set-up (artists that feature on a mixtape album) as with the Sample CRUD App the results of what you do in the forms don't seem to populate in the tables afterwards and I also wanted to see the results in SQL Server.

---

Table structure

Mixtape
MixtapeID (PK, int, not null)
MixtapeName (nvarchar(255), not null)

MixtapeArtist
RelationshipID (PK, int, not null)
MixtapeID (FK, int, not null) - to MixtapeID in Mixtape
ArtistID (FK, int, not null) - to ArtistID in Lookup_Artist

Lookup_Artist
ArtistID (PK, int, not null)
ArtistName (nvarchar(255), not null)

CREATE TABLE [ShashamaneDubplates].[Mixtape] (
MixtapeID INT IDENTITY(1,1) PRIMARY KEY, 
MixtapeName NVARCHAR(255) NOT NULL,
) 
GO 

CREATE TABLE [ShashamaneDubplates].[MixtapeArtist] (
RelationshipID INT IDENTITY(1,1) PRIMARY KEY, 
MixtapeID INT NOT NULL,
ArtistID INT NOT NULL,
) 
GO

CREATE TABLE [ShashamaneDubplates].[Lookup_Artists] (
ArtistID INT IDENTITY(1,1) PRIMARY KEY, 
ArtistName NVARCHAR(255) NOT NULL,
) 
GO

ALTER TABLE [ShashamaneDubplates].[MixtapeArtist]  
WITH CHECK 
ADD CONSTRAINT [FK_ArtistLookup] FOREIGN KEY([ArtistID])
REFERENCES [ShashamaneDubplates].[Lookup_Artist] ([ArtistID])
GO

ALTER TABLE [ShashamaneDubplates].[MixtapeArtist] 
CHECK CONSTRAINT [FK_ArtistLookup]
GO

ALTER TABLE [ShashamaneDubplates].[MixtapeArtist]  
WITH CHECK 
ADD CONSTRAINT [FK_MixtapeLookup] FOREIGN KEY([MixtapeID])
REFERENCES [ShashamaneDubplates].[Mixtape] ([MixtapeID])
GO

ALTER TABLE [ShashamaneDubplates].[MixtapeArtist] 
CHECK CONSTRAINT [FK_MixtapeLookup]
GO

INSERT INTO [ShashamaneDubplates].[Mixtape] (MixtapeID, MixtapeName) 
VALUES ('1', N'Volume 1'),('2', N'Volume 2'),('3', N'Volume 3') 
GO

INSERT INTO [ShashamaneDubplates].[Lookup_Artists] (ArtistID, ArtistName) 
VALUES ('1', N'Duane Stephenson'),('2', N'Little Hero'), ('3', N'Tarrus Riley'),('4', N'Half Pint'),
('5', N'Johnny Osbourne'),('6', N'Cocoa Tea'),('7', N'Da''ville'),('8', N'Dynamq'),
('9', N'Roger Robin'),('10', N'Josey Wales') 
GO

---

The tree of handlers looked slightly different to your example in places but I tried to inject the correct properties in place at the right times. I didn't write over any event handlers as it started causing errors when I did. This was the setup:

Taking into account all the above, the application loads upon pressing run but when going to edit a relationship the artists don't appear in the drop down, as below:

Can you see anything that I've missed out here?

[korchev]

Hi James,

I can't see anything suspicious. Can you send us a backup of your DB (or SQL create script) and the meta directory of your application to info@radzen.com? We will inspect the application and see what could be missing.

[korchev]

Do you have the invoke custom method code which updates the database? It should be invoked in the Submit event of your form. I couldn't see it in those screenshots.

[DoubleCarpet]

@korchev

Hi Atanas,

I was only concentrating on getting this part right first, I thought I'd be able to do the first part, see if the form behaviour worked and then look at the server side part. Or do I need to do all of it first?

The create table statements are at the top of the code section of my first post - is that enough for you to be able to replicate it?

[korchev]

@DoubleCarpet,

I will also need the meta directory to see the whole picture.

[DoubleCarpet]

@korchev

Do you need the whole meta folder? How can I upload that for you to see?

[korchev]

Yes, we need the whole meta directory. Just zip it and send it over email to info@radzen.com.

[OKLMike]

@korchev
Hi, new to Radzen and trying it out.
Should the sample that you gave above (https://github.com/akorchev/radzen/tree/master/ManyToManyWithMultipleSelect) work with the current release of Radzen?
I've downloaded it from github, imported into radzen, but get the following error when I run it:

ng-cli:
ERROR in node_modules/rxjs/Subscription.d.ts(1,15): error TS2307: Cannot find module 'rxjs-compat/Subscription'.
src/app/add-order/add-order-generated.component.ts(9,10): error TS2305: Module '"C:/Users/michael.boyes/Desktop/radzen-master/radzen-master/ManyToManyWithMultipleSelect/client/node_modules/rxjs/Subscription"' has no exported member 'Subscription'.
src/app/edit-order-detail/edit-order-detail-generated.component.ts(9,10): error TS2305: Module '"C:/Users/michael.boyes/Desktop/radzen-master/radzen-master/ManyToManyWithMultipleSelect/client/node_modules/rxjs/Subscription"' has no exported member 'Subscription'.
src/app/order-details/order-details-generated.component.ts(9,10): error TS2305: Module '"C:/Users/michael.boyes/Desktop/radzen-master/radzen-master/ManyToManyWithMultipleSelect/client/node_modules/rxjs/Subscription"' has no exported member 'Subscription'.

ng-cli: i ｢wdm｣: Failed to compile.

[OKLMike]

Anyone got any advice on how to get the sample working? Does it import ok for you?

[korchev]

You can delete the files with the compilation errors. They are remnants from deleted pages. We have also updated the project in github with the latest bits.

[maximnl]

actually this is very useful feature, multiple selected values could be written to a string field with a choice of a delimiter, it is really basis and if a RAD framework does not support this baby steps, it is not RAD.

[korchev]

Hi @maximnl,

Welcome to the Radzen community and thank you for sharing your feedback with us.

To be honest I haven't seen any application that stores multiple relationships as a comma separated string in a database. I think using a junction table is considered a best practice. We have linked a sample project in this thread. It shows how to use such tables in Radzen.