Database view between alm_hardware and sn_hamp_m2m_audit_asset table

[SanketKumaS]

Could anyone please help on how to create a database view between alm_hardware and sn_hamp_m2m_audit_asset table? I want the audit status from M2M table to be displayed on the report with the corresponding audit number field on the alm_hardware table.

[pr8172510 · Accepted solution]

Hey @SanketKumaS,

Navigate to System Definition > Database Views and click New .

1.  Add the Main Table (alm_hardware)

In the View Tables related list, add your first table:

• Table: alm_hardware

• Order: 100

• Variable Prefix: hw

  2. Add the Second Table (sn_hamp_m2m_audit_asset) with a Join

Now add the M2M table to link it:

• Table: sn_hamp_m2m_audit_asset

• Order: 200

• Variable Prefix: audit

• Where clause: hw.sys_id = audit.asset

    3. Add Your Fields

   Next, go to the View Fields related list. Click New and select the specific fields you want to bring into your report:

• From alm_hardware: Pick asset_tag, serial_number, model, etc.

• From sn_hamp_m2m_audit_asset: Add audit_status and the reference to the audit record itself. That audit field will link directly back to the audit header record you need .

      4.Save & Test

Save the Database View, then click the Try It . 

[pr8172510 · Accepted solution]

Hey @SanketKumaS,

Navigate to System Definition > Database Views and click New .

1.  Add the Main Table (alm_hardware)

In the View Tables related list, add your first table:

• Table: alm_hardware

• Order: 100

• Variable Prefix: hw

  2. Add the Second Table (sn_hamp_m2m_audit_asset) with a Join

Now add the M2M table to link it:

• Table: sn_hamp_m2m_audit_asset

• Order: 200

• Variable Prefix: audit

• Where clause: hw.sys_id = audit.asset

    3. Add Your Fields

   Next, go to the View Fields related list. Click New and select the specific fields you want to bring into your report:

• From alm_hardware: Pick asset_tag, serial_number, model, etc.

• From sn_hamp_m2m_audit_asset: Add audit_status and the reference to the audit record itself. That audit field will link directly back to the audit header record you need .

      4.Save & Test

Save the Database View, then click the Try It . 

[SanketKumaS]

Thank you. It worked.

[Tanushree Maiti · Accepted solution]

Hi @SanketKumaS 

Try with this:

Table	Order	Variable Prefix	Where Clause
Hardware (alm_hardware)	100	hw
Asset Audit M2M (sn_hamp_m2m_audit_asset)	200	m2m	m2m_asset = hw_sys_id

[SanketKumaS]

Thank you.This also worked.