xTuple.com xTupleU Blog & News Customer Support

Charass confusion

Technology
#1

   I was getting ready to start importing item characteristics into the charass table.  By chance I noticed some odd entries, and I'm not sure if they are supposed to be there or if I should remove them.  The only characteristics entered, aside from those that come with xTuple, was a brand name characteristic I imported using the CSV import tool a month or so ago.  These had a charass_target_type text value, "I", for Item I would assume.  I found some other entries in that column that I could not immediately identify, especially since they all had the same charass_char_id value for the, "Brand" characteristic, and the, "Brand" characteristic is configured to only effect the item characteristics.  I see charass_target_type values of, "PI, QI, R, SI, and W" in the table.  Moreover, all of the entries with these charass_target_type are either duplicates with the wrong charass_value, have a charass_target_id that doesn't exist, or have a charass_target_id that represents a product that should have no brand assignment.  This seems alarming, except for that everything is working properly and displaying the correct brand, or lack there of in xTuple.   

   Does anybody know a reason why I shouldn't clear these out?  Does anybody know what the charass_target_type values of, "PI, QI, R, SI, and W" stand for?  Has anyone else experienced anything like this?

#2

First an answer to your question of the charass_target_type values:

PI: Purchase Order item
QI: Quote Item
R: this one has me scratching my head, unless its a typo, typically the R’s are for Return Authorization (RA) or Return Authorization Item (RI)…
SI: Sales Order Item
W: Work Order

When you define a characteristic and associate it with Items (charass_target_type ‘I’), it is implied that you can use that characteristic in the places that the item can be used, such as on a Sales Order Item, if you assigned “Brand” to an item you should be able to see that Brand on the sales order line item, so it makes sense to me that the PI/QI/SI/W types have entries, if you created those line items for an Item in your database, the characteristics would follow it throughout the application.

The reason I ask what version of xTuple is because as of version 4.9.0 we modified the characteristics structure a bit in order to make them extensible. Two things to point out, one being a table called “source”, which is now the central location for information such as the charass_target_type values, if you look in the source table you will see the column “source_charass”, which is where I pulled those target_types from. Additionally, there used to be columns on the char table to let the user indicate where that characteristic can be used (such as char_items, char_contacts, etc), however those columns have been deprecated in favor of a new cable called “charuse”. This table is what keeps the selections of where your characteristics can be used. So if you choose to allow Brand to be used on Items, there should be an entry in charuse that maps your char_id to the charuse_target_type = ‘I’.

We generally don’t recommend clearing anything out unless its invalid data, but in this case I think you are just seeing artifacts of how the characteristic system flows throughout the application and the data is fine on its own. At minimum a row of data will take up the size of a block on the hard drisk, usually around 4KB for smaller rows like charass… this won’t have much impact on your database performance at all.

Lastly, I think you may have brought to light something that may be confusing, in that prior to 4.9.0 specifying an characteristic can be used on Items allowed it to be used in the places an Item can be used, however as of 4.9.0 the individual places have been broken out. Meaning, there are now checkboxes for Sales Order Item and Purchase Order Item, and checking that a characteristic can be used for an Item still implies those even though they haven’t been explicitly selected. I will bring it up to our development staff and see how we want to handle that. Thank you for bringing it to our attention –

Hope this helps, let me know if you have any further questions.

#3

   Thanks for the help David.  Yes, that was an, "R", and there is not an "R" in source_charass.  I'm glad I'm not the only one scratching my head.  That was a good tip on the sources table.  I'm using version xTuple 4.9.3 on PostgreSQL 9.3 on Xubuntu 14.04.  The charuse_target_type is set to, "I" as it should be.

   If "I" is implied to operate on PI, QI, SI, & W types, the results in the charass table don't make sense.  I have 827 entries that appear potentially incorrect.

178 rows are target_type QI, W, PI or R and have a conflicting value as compared to an existing value for the same target_id of target_type "I". 

194 rows are target_type QI, W, PI or R and have the same value as compared to an existing value for the same target_id of target_type "I".  These may be correctly entered

106 rows are target_type QI, W, PI or R and have a target_id that doesn't exist in the items table or has a target_id for an item that wouldn't normally have a brand characteristic at all.

349 rows are target_type SI or R with a terget_id over the highest existing value in the items table.  This one is the most troubling to me

I only have 6016 entries, including those above.  The direction we are moving, we will have tens or hundreds of thousands of entries in this table.  If this, in part or whole, is a trend, I should see between 5-14% of the records related to other documents.  I'm not sure if that should be cause for concern.

I went back and looked at my original CSV, and found nothing similar to what I described above.

 

#4

I will explain a little bit of the internal characteristics flow as I think it will clear some confusion. As you know, characteristics are user defined attributes that you can associate with business objects such as Items, but also things like Sales Orders and Purchase Orders. When associating characteristics with Items, those characteristics can either be related to the item itself (such as Brand), or related to a specific instance/usage of that item. I will use an example item from the demo database: YTRUCK1.

On the item master, YTRUCK1 has the characteristic “I-FINISH” with a value of “Gloss” and it is marked as the default choice. This means there should be a row in charass with charass_target_type = ‘I’, charass_value = 'GLOSS" and the char_id and target_id’s pointing to the proper ids, target_id being in the item table in this instance (since charass_target_type = ‘I’) and char_id pointing to the id in char. In this instance it is something that may be configurable, maybe we can offer Matte finishes too, though in your instance “Brand” is unlikely to change.

Create a sales order to any customer, add a line item for YTRUCK1 and click on the characteristics tab. Notice you have the I-FINISH and other characteristics already there and the defaults chosen (if there was a default set on the item master). Save the line item. Now in charass, you should have another row, this time charass_target_type = ‘SI’, charass_value = ‘Gloss’ and a new charass_target_id. This new record represents the association of this characteristic and value to your new Sales Order Item (SI) id, which lives in the coitem table. You can find that association using the source table in the column called source_table. This is a specific instance of an association between that item, its use on a sales order, and that characteristic.

Now, here is where things can diverge a little bit. If you defined the characteristic as type “Text”, users can enter whatever they want into that field, which would easily explain why the QI/PI/W target_types have different values than what you originally specified. So you could have rows for the Brand characteristic associated with a Sales Order Item that contain different values of Brand. If you create the characteristic as a List, they are forced to choose from an existing value to prevent variations. Also, if you don’t specify that it is the “Default”, the characteristic won’t automatically be assigned to the sales order item if you didn’t explicitly choose one (or type in something).

With that, lets go over your concerns:

194 rows are target_type QI, W, PI or R and have the same value as compared to an existing value for the same target_id of target_type “I”. These may be correctly entered
These were created by the software automatically when you created a line item for whatever Item the characteristic is associated with. No one touched the characteristics tab, so the default got saved and lines up with the item record.

178 rows are target_type QI, W, PI or R and have a conflicting value as compared to an existing value for the same target_id of target_type “I”.
106 rows are target_type QI, W, PI or R and have a target_id that doesn’t exist in the items table or has a target_id for an item that wouldn’t normally have a brand characteristic at all.
349 rows are target_type SI or R with a terget_id over the highest existing value in the items table. This one is the most troubling to me
Each of these are the same confusion, each target_type points to a different table, so if you had charass_target_id = 123 and charass_target_type = ‘QI’, you would find a row in quitem for id 123. As mentioned above you can find these associations using the source table. We compiled this “source” table in an effort to centralize all the different “types” we use throughout the software, such as in characteristics, but also on comments or document associations. This also allows administrators and developers to add in their own types.

The one troubling thing to me is that I am still unsure of what type ‘R’ is. When I get a moment I will dig into finding out where that is coming from. Do you use the RMA part of xTuple (requires a commercial version)? I wonder if it is a typo on our part…

#5

I use "List" for characteristics to reduce the number of errors.  But if the target_id is pointing to a different table, then it is probably okay. 

"Also, if you don't specify that it is the "Default", the characteristic won't automatically be assigned to the sales order item if you didn't explicitly choose one (or type in something)."

I think this is my problem right here.  I set all of the brand characteristics to "Default".  If I switch these back, that should start working more the way I was expecting it to.

We sometimes use the "Return" feature under, Sales>BIlling>Return.  Is that the same thing?

Thanks for taking the time to explain this to me.