Purchase Request (PR) raised by purchase type
Kissflow Work Platform:
- Small Business
If a company's procurement manager wishes to examine purchase requests in terms of the number of requests raised, the type of request, and the items requested, they have to join two tables using lookups to obtain the report covering data from two tables.
The following is a sample query for creating this dataview:
prcat."Item" as Catalog_Item,
prcat."Quantity" as Catalog_Item_Quantity,
prcat."Item_Amount.value" as Catalog_Amount,
prncat."Item_1" as Non_Catalog_Item,
prncat."Quantity_1" as Non_Catalog_Item_Quantity,
prncat."Item_Amount_1.value" as Non_Catalog_Amount,
pr."PR_Estimate.display_value" as TOTAL_PR_VALUE
"Purchase_Request" as pr
left outer join "Catalog_Items" prcat on pr."_id" = prcat."_instance_id"
left outer join "Non_Catalog_Items" prncat on pr."_id" = prncat."_instance_id"
In the above query, Purchase_Request is the process table, and Catalog_Items and Non_Catalog_Items are its child tables. An SQL join is performed between the process table and its child tables.
After the dataview is created, the procurement manager can use the dataview to create a pivot report with the following configuration.