Add an option to set a custom field as unique

[ashrafalzyoud]

For instance I need to create Custom Field storing an OrderID (entered manually) and I want it to be unique across all tickets. Is there a way to achieve it?

[AirTibu]

Hi,

I'm very new to using Redmine and I'm not a programmer, but I would solve the problem as follows:

if CustomValue.where(:custom_field_id => 181).pluck(:value).include?(self.custom_field_value(181)) && self.custom_field_value(181) != self.custom_value_for(181).try(&:value)
    errors.add(:base,"This order id is already in use!")
end

You have to change each number 181 to the custom_field_id of you OrderID field.

The first part is checking, that the OrderID is exist or not, the second part is necessary, because once you create the order ID, the first part will be always TRUE, so re-checking the orderID field is only necessary if you change the value of the field.

I hope this helps!

[ashrafalzyoud]

thx for replay me but where added this formula

[AirTibu]

You should add to before save section in custom workflow plugin.

[ashrafalzyoud]

Thx for u brother

[ashrafalzyoud]

HI @AirTibu MY manger ask me this if cfs[10] type( list or link value) and cfs[11] integer we need these compare values and not duplicated look plz to this table

[AirTibu]

Hi,

A custom field can have only 1 type, so I do not understand why we have to check it. What is the type of your cfs[10]?

[ashrafalzyoud]

Cfs[10]= "Key/value List" Cfs[11]=integer Must these values not duplicated Like table example

[ashrafalzyoud]

@AirTibu if u can help ?

[AirTibu]

Hi,

The problem is, that not easy to find solution for you, because the request is not clear. I have problem with this:

"if cfs[10] type( list or link value)"

You stated, that your custom field (cfs[10]) is "Key/value List". You can test the type of this field with this code:

CustomField.find(10).field_format.to_s

The result always be "enumeration" and never by "list or link".

But if your key value list contains link and some list, than you can check the value itself:

@cf_key_value = CustomFieldEnumeration.where(:id=> custom_field_value(187).to_s,:custom_field_id => '187').first.to_s
if @cf_key_value.include?('https://')
 # do things
end 

We have the same issue with Cfs[11] field. Its type is integer, so the value always be integer. We can test it like this:

self.custom_field_value(11).to_i.to_s == self.custom_field_value(11)

Nevertheless, the code you are looking for:

if self.custom_field_value(11) != self.custom_value_for(11).try(&:value) || self.custom_field_value(10) != self.custom_value_for(10).try(&:value)

    @cf_key_value = CustomFieldEnumeration.where(:id=> custom_field_value(10).to_s,:custom_field_id => '10').first.to_s
    @cfs_10_is_link = @cf_key_value.include?('https://')  || @cf_key_value.include?('www.') 

    @cfs_11_is_integer = self.custom_field_value(11).to_i.to_s == self.custom_field_value(11)

    @cfs_10_issues =  CustomValue.where(:custom_field_id => 10,:value => self.custom_field_value(10).to_s ).pluck(:customized_id) 
    @cfs_11_issues = CustomValue.where(:custom_field_id => 11,:value =>self.custom_field_value(11).to_s ).pluck(:customized_id)
    @combination_exists = !(@cfs_10_issues & @cfs_11_issues).empty?

        if @cfs_10_is_link && @cfs_11_is_integer && @combination_exists
            errors.add(:base,"This cfs 10 and cfs 11  combination is exists!")
        end    

end

But this code is testing only for LINK and do not test the list. Please send a screenshoot about your key value lists, the I can check it.

[ashrafalzyoud]

im sorry about wronge (link) word In my start ticket by mistkate i mean if cfs[10] tybe ( list or key/value lists) my list as example (A,B,C,D,E) OR my key/value list (A,B,C,D) i dont need link my cfs [11] integer ---> cfs [10] & cfs [11] not duplicated 1 & A OK 1& B OK 1& A ERROR DUPLICATED 2 & A OK like table up

[ashrafalzyoud]

MY KEY/VALUE LIST MY ISSIUE

[ashrafalzyoud]

IF cfs[10] type list my possible value as example

[ashrafalzyoud]

@AirTibu Thanks for your cooperation

[AirTibu]

Hi,

In the case, put this code to BEFORE SAVE section:

if self.custom_field_value(11) != self.custom_value_for(11).try(&:value) || self.custom_field_value(10) != self.custom_value_for(10).try(&:value)

    @cfs_10_issues =  CustomValue.where(:custom_field_id => 10,:value => self.custom_field_value(10).to_s ).pluck(:customized_id) 
    @cfs_11_issues = CustomValue.where(:custom_field_id => 11,:value =>self.custom_field_value(11).to_s ).pluck(:customized_id)
    @combination_exists = !(@cfs_10_issues & @cfs_11_issues).empty?

    errors.add(:base,"This cfs 10 and cfs 11  combination is exists!") if @combination_exists

end

[ashrafalzyoud]

You are a great and helpful person thats what i want

[AirTibu]

Great! I’m happy, that’s work! Please do not forget to close the tickets, if your problem has been solved.

[ashrafalzyoud]

@AirTibu IF cfs[10] type list and cfs [11] type integer not work in last code

[AirTibu]

Hi,

The only explanation can only be, that your cfs[10] is not always cfs[10], so you would like to use this code for other custom fields too.

This code solves all your problem, this is generic, you should change only the numbers (custom_field_ids), when you call function:

check_duplication(integer_cf_id,list_cf_id) # Like this ---> check_duplication(11,10) The code:

def check_duplication(integer_cf_id,list_cf_id)
    if CustomField.find(list_cf_id).field_format == 'list' && CustomField.find(integer_cf_id).field_format == 'int'
        if self.custom_field_value(integer_cf_id.to_s) != self.custom_value_for(integer_cf_id.to_s).try(&:value) || self.custom_field_value(list_cf_id.to_s) != self.custom_value_for(list_cf_id.to_s).try(&:value)
            cfs_list_issues =  CustomValue.where(:custom_field_id => list_cf_id.to_s,:value => self.custom_field_value(list_cf_id.to_s).to_s ).pluck(:customized_id) 
            cfs_integer_issues = CustomValue.where(:custom_field_id => integer_cf_id.to_s,:value =>self.custom_field_value(integer_cf_id.to_s).to_s ).pluck(:customized_id)
            combination_exists = !(cfs_list_issues & cfs_integer_issues).empty?
            errors.add(:base,"This cfs #{integer_cf_id} and cfs #{list_cf_id}  combination is exists!") if combination_exists
        end
    end
end

check_duplication(11,10)

Add this code to BEFORE SAVE action and it have to start with this code!

[ashrafalzyoud]

its not working also its becuase Multiple values in custom field in type list
when i remove the check its working

[AirTibu]

Hi,

If you are using multiple values list, then use this code:

  def multi_custom_value_changed?(cf_id)
      Issue.find(@issue.id).custom_field_value(cf_id).uniq.sort != @issue.custom_field_value(cf_id).uniq.sort if @issue.custom_field_value(cf_id)
  end

  def custom_value_changed?(cf_id)
      custom_field_value(cf_id) != custom_value_for(cf_id).try(&:value)
  end

  def check_duplication(integer_cf_id,list_cf_id)
      if CustomField.find(list_cf_id).field_format == 'list' && CustomField.find(integer_cf_id).field_format == 'int'
         cfs_list_issues =  CustomValue.select("customized_id, string_agg(Distinct value,',')").where(custom_field_id: list_cf_id.to_s ).group(:customized_id).having("string_agg(Distinct value,',') = '#{[@issue.custom_field_value(list_cf_id)].join(',').to_s}'").pluck(:customized_id)
         cfs_integer_issues = CustomValue.where(:custom_field_id => integer_cf_id.to_s,:value =>self.custom_field_value(integer_cf_id.to_s).to_s ).pluck(:customized_id)
         combination_exists = !(cfs_list_issues & cfs_integer_issues).empty?
         errors.add(:base,"This cfs #{integer_cf_id} and cfs #{list_cf_id}  combination is exists!") if combination_exists
      end
  end

check_duplication(11,10) if custom_value_changed?(11) || multi_custom_value_changed?(10)

I hope this helps!

[znkey]

Issue.find(@issue.id).custom_field_value(cf_id).uniq.sort != @issue.custom_field_value(cf_id).uniq.sort if @issue.custom_field_value(cf_id)

This code help to solve my problems ! Thank you man! @AirTibu