How do I combine zoho creator records from many different forms?

I will be importing existing data from another system, and I'm trying to use Creator's interface to create a items dispatch system for the warehouse or third party logistics (which I think is a pretty common use case).

So we have an item table, an order table, an order detail table (contains line items), and a customer table.

Let's say a warehouse personnel needs to look at the list of items to be prepared, he needs to retrieve physical items per order, based on a "need by" date in the order table, how is this achieved in Creator?

Let's further assume that the warehouse has an automatic retrieving system which takes some time to move the goods, and the same item repeats itself in across multiple orders. It would make sense to be able to display and batch them up in one retrieval.

In other words, is it possible to perform an sql-like join on all the forms/records and generate a report that is filterable? Is there also a SUM() and GROUP BY function?

It seems to be a waste of resources to

- perform multiple loops (or inner loops!) to retrieve this data

- create one huge form/record to squeeze everything based each order detail's line item