• Replies 5 replies
  • Subscribers 41 subscribers
  • Views 10512 views
  • Users 0 members are here
Options
Related

Set "Item has BOM"-indicator

mkalla
Offline

Hi,

To indicate that a Part has BOM or not I want to set a field (HasBOM) to 1 or 0

I have below server side code  triggered on onBeforeAdd and  OnBeforeUpdate on the Part item type

It works,

But I have to press save twice!? to have the field updated after I removed or added a BOM,
is there a way to have it updated directly on save???

Thanks for any idea

//Mikael

// Method to check if item has BOM and set HasBOM "

Innovator inn = this.getInnovator();
string issueID = this.getProperty("source_id","");

Item updatedBOM = inn.applyAML( "<AML>" + " <Item type='Part BOM' action='get' select='related(item_number)'>" + " <source_id>" + this.getID() + "</source_id>" + " <related_id>" + " <Item>" + " </Item>" + " </related_id>" + " </Item>" + "</AML>");


if (updatedBOM.getItemCount() > 0)

{
this.setProperty("hasbom", "1");
}
else
{
this.setProperty("hasbom", "0");
}

return this;

Parents

  • Hi Mikael,

    please check out the Files flag used in Manufacturer Parts. It does something similar and works very reliable. The corresponding Methods can be found as Server Events in ItemType Manufacturer Part Files. But you have to change the corresponding SQL Procedure.

    Angela

  • Offline in reply to AngelaIp

    Hi Angela, thanks

    I tried it and modified the code

    But requires that you only have one relationship-name named:  "Part"

    I had for ItemType Part, two relationships ("Part BOM" and "Part Alternate") both with name "Part"

    When I removed the Alternates Tab, then it worked

    So how could I keep the Part Alternates-relationship and have the code to work?

    My SQL (based on SQL "update_has_files_flag")

    create procedure HasBOMFlag(@itemtypename nvarchar(32),@itemID char(32))
    AS
    BEGIN
    DECLARE @relationship_db_name nvarchar(32)
    DECLARE @itemtype_db_name nvarchar(32)

    SET @itemtype_db_name = (select instance_data from [ITEMTYPE] where name = @itemtypename)

    SET @relationship_db_name = (select instance_data from [ITEMTYPE] where
    id = (select relationship_id from [RELATIONSHIPTYPE] where
    source_id = (select id from [ITEMTYPE] where name = @itemtypename)
    AND
    related_id = (select id from [ITEMTYPE] where name = 'Part')))

    DECLARE @SQLstr nvarchar(500)
    DECLARE @ParmDefinition nvarchar(500);
    SET @ParmDefinition = N'@id char(32)';

    DECLARE @count int
    SET @SQLstr = N'UPDATE [' + @itemtype_db_name + ']
    SET hasbom = CASE
    WHEN (select count(id) FROM [' + @relationship_db_name + '] where SOURCE_ID = @id) > 0
    THEN ''1''
    ELSE ''0''
    END
    WHERE ID=@id';

    EXECUTE sp_executesql @SQLstr, @ParmDefinition, @id=@itemID

    SET @SQLstr = N'SELECT * FROM [' + @itemtype_db_name + '] WHERE ID = @id'

    EXECUTE sp_executesql @SQLstr, @ParmDefinition, @id=@itemID
    END

Reply
  • Offline in reply to AngelaIp

    Hi Angela, thanks

    I tried it and modified the code

    But requires that you only have one relationship-name named:  "Part"

    I had for ItemType Part, two relationships ("Part BOM" and "Part Alternate") both with name "Part"

    When I removed the Alternates Tab, then it worked

    So how could I keep the Part Alternates-relationship and have the code to work?

    My SQL (based on SQL "update_has_files_flag")

    create procedure HasBOMFlag(@itemtypename nvarchar(32),@itemID char(32))
    AS
    BEGIN
    DECLARE @relationship_db_name nvarchar(32)
    DECLARE @itemtype_db_name nvarchar(32)

    SET @itemtype_db_name = (select instance_data from [ITEMTYPE] where name = @itemtypename)

    SET @relationship_db_name = (select instance_data from [ITEMTYPE] where
    id = (select relationship_id from [RELATIONSHIPTYPE] where
    source_id = (select id from [ITEMTYPE] where name = @itemtypename)
    AND
    related_id = (select id from [ITEMTYPE] where name = 'Part')))

    DECLARE @SQLstr nvarchar(500)
    DECLARE @ParmDefinition nvarchar(500);
    SET @ParmDefinition = N'@id char(32)';

    DECLARE @count int
    SET @SQLstr = N'UPDATE [' + @itemtype_db_name + ']
    SET hasbom = CASE
    WHEN (select count(id) FROM [' + @relationship_db_name + '] where SOURCE_ID = @id) > 0
    THEN ''1''
    ELSE ''0''
    END
    WHERE ID=@id';

    EXECUTE sp_executesql @SQLstr, @ParmDefinition, @id=@itemID

    SET @SQLstr = N'SELECT * FROM [' + @itemtype_db_name + '] WHERE ID = @id'

    EXECUTE sp_executesql @SQLstr, @ParmDefinition, @id=@itemID
    END

Children
No Data