Extending / Duplicating a Resource to create a new or different output 

Often an Orixa App contains a Resource which generates some useful output, but the user decides they need "something more". This webpage shows the steps that should be taken to achieve this, from a practical example.

The Existing Resource: A Cube which shows farmer-acreage data

Farmer Organic status cube  

The above image shows a data-cube of:

1. Cocoa and Oil Palm acreages and yield-estimates
2. with totals by farmer certification status.
3. Each total includes a "drill down" to show the list of individual farmers with their individual acreages and yield estimates.

The Cube is produced from a Resource record in the Resources data-table. Users now want to extend their system to add new crops to the output.

Resource used to generate the farmer-acreage Cube  

1. One record in the Resources data-table includes all the information needed by the Application to create the Cube.
2. A complex SQL statement pulls back data from the database.
3. Choosing the "ComponentID"  "Cube" means that the data will be generated as a data-cube. Users can design this cube themselves within the App. Once they have a design they like, they can save it. The details of the design are saved in the Resource's "ObjectProperties" data-field.

Duplicating This Resource

Duplicating a resource  

Rather than re-writing the entire resource to generate a new version, just "Duplicate this record", as shown above. This is a very useful function, especially when a lot of work has been done to design a resource.

Once the new record is created, adjust the SQL to pull back new data, in this case data for other crops.

How to re-write the SQL

Example SQL for the Resource (simplified)

SELECT
  COALESCE(SUBSTRING(S.Name FROM 0 FOR 3), 'NA') as OrganicStatus,
  F.FarmerCode,
  FI.Crop,
  P.FullName as Farmer,
  FI.SumYieldEstimate / 1000 as YieldEstTon,
  FI.Hectares
FROM Farmers F
  LEFT JOIN People P ON (F.ID = P.ID)
  LEFT JOIN Status S ON (F.StatusID = S.ID)
  LEFT JOIN
    (SELECT
        I.PersonID,
        FF.MainCropID,
        C.Name as Crop,
        SUM(FF.YieldEstimate) as SumYieldEstimate,
        SUM(FF.Hectares) as Hectares
      FROM Inspections I
        LEFT JOIN FarmFields FF ON FF.InspectionsID = I.ID
        LEFT JOIN Crops C ON C.ID = FF.MainCropID
      WHERE MainCropID IN (539, 553) 
        AND I.DateDone BETWEEN DATE [MinDate] AND DATE [MaxDate]
        AND FF.Current = true
      GROUP BY I.PersonID, MainCropID) FI ON (FI.PersonID = F.ID)
WHERE Crop IS NOT NULL
ORDER BY OrganicStatus, FarmerCode

Note the line from the above script:

WHERE MainCropID IN (539, 553)

The Cube is only showing the details of 2 crops. This WHERE clause is the place in the script where these 2 crops are selected. How can we add other crops? We just need to add additional IDs to this list

Finding the ID of Crops to use in the resource

The Crops Grid  

Open the Crops Grid, and look at the data. The 2 crops which appear in the SQL are marked with arrow. You can see their IDs: 539 and 553. 

To add other crops to the newly created resource, simply pick out their IDs and use them in the SQL. There is even a small feature of Orixa to make this step easier.

Copying the ID for "Maize" to the clip board to use in the SQL  

1. Open the Crops Grid and double click on a Crop you want to add to the new Resource. In this case we have selected "Maize".
2. Right-click on the "ID" field in the Edit Window and click "Copy to Clip-board"

Now go to the SQLStr field of the newly duplicated Resource and paste this copied value into the line:

WHERE MainCropID IN (539, 553, 20003617)

The Cube will now show data for 3 crops, including Maize.