Skip to main content

Hello, 

I have two custom tables in a custom screen (Preferred Technician) that I will use for recording clients' technician preferences.

Custom Table A – Parent

Custom Table B – Child 

A custom global code table – Technician Ranking (1-5)

Scenario:

In the custom screen (Preferred Technician), In the header (Table A will be used here), I have, for example, the "Set#", "Product Family", and "Client ID" fields. I can select the product family and client ID, then click SAVE, and a Preferred Technician Set will be created.

I will then go to the Preferred TECH to add the preferred Technician for this Set#. All the preferred Tech lines will be saved in Table B. Each Set# can have max five preferred technicians, and these technicians should be ranked.

The same technician cannot be reused within the same Set#, nor can the ranking number be reused. Therefore, I cannot set the Ranking field to "is unique" in Table B, as it would prevent reuse of the same ranking number in other sets. I have tried the "is key" checkbox, but it did not help.

I know I can use a BR or client script to check these ranking but still I would like to know if there are any other options, aside from using BR or client scripts, that can solve this issue? Is it possible to solve this with metadata config?

Thanks in advance😊

 

Hi @ChingL ,

You should be able to uniquely identify the technician and the ranking if you put is_key option checked in the custom metadata table. In this case, it will work as a composite key. Therefore, in your case the primary keys should be the set_id, technican and the ranking. If this logic is in place, when you try to duplicate the record with same technician with the same ranking, fsm will throw a primary key violation error as expected from the metadata level. 

Make sure to deploy the table script (db scrtipt) into SQL query tool once you made the change. Then refresh cache and test it.

Additionally, you may need to create a client script or BR to validate the maximum number of 5 records per set. This is not something that you can achieve with fsm metadata.

Hey @ChingL @SAMLK 

The approach suggested by @SAMLK will not resolve your requirements completely.

As I understand, it still will be possible to create duplicates.

For example, below are records with 3 fields as keys. Each row is unique, but the combination violates the requirements. i.e. still can have duplicate Rank and/or Techs.

 

Set Tech Rank
23 Tech1 1
23 Tech1 2
23 Tech2 2
etc. etc. etc.

 

Cheers!

 

@Shneor Cheshin my bad, I have not read the content properly. 😄 I thought that the same technician should not be repeated with the same ranking.

23 Tech1 1
23 Tech1 1

If this is the case, then you will need to use client script or a BR. You can’t decide this by indicating either one of the technician nor ranking field as primary key based on the input

@SAMLK is there any example client script or a BR?

Reply


Terms & ConditionsCookie settings