Are you using our SaaS platform (Baserow.io) or self-hosting Baserow?
Self-hosted
What do you need help with?
Hi Folks - a newbie to the world of API and Webhooks..
Looking for ideas to be able to construct a simple report writer (using ODBC + MS Access + SQL which will do fine) that enables me to join tables on one or more non-primary joins) and use filters etc to output simple listings in EXCEL. I cannot use ODBC direct on Baserow tables because of the internal of how the data is stored in arrays etc..
Ideas on best way forward ?
Yes - manually I can download csv exports from each table - map into ACCESS - and that would work - but out of date quickly and manual effort every time.
If I manage to create an on-demand and timed script/mechanism that extracts all the records in a nominated Baserow table using the API and inserts it into a new postgresql table in the server (outside of Baserow) then I can use plain ODBC on a plain structured old-fashioned table layout.
The immediate question is : does the API extract “re-assemble” the record as I see in the Baserow table (just as the csv does)??
Hey @mikemitch
Yes, the API reassembles the record just like the CSV export does. When you call the list-rows endpoint (GET /api/database/rows/table/{table_id}/?user_field_names=true), you get back clean JSON where each row has human-readable field names and resolved values.
But real question is what problem are you trying to solve with excel and odbc? It is possible that it can be solved completely within baserow without separate scripts.
1 Like
Hi - thanks for getting back to me…..I need a simple report writer for Baserow.
I have emulated an old Oracle database system in 65 table with about 80 datafields on each. There will be 100’s of users. People will ask for almost anything as a quick EXCEL listing.
What I find odd about all the lonocode products - there is no virtual tables capability where I can build a VIEW (not another table) ACROSS one or more tables - and crucially - the link is NOT the primary key.
Imagine 2 tables…a Delivery Table of 10 datafields and a Receipt Table. Primary key in both is Field 1 - basically a unique document number - different in each table. In the Delivery table imagine datafield 8 is the sent date. A user will ask for a quick report - “show me all deliveries that have not yet arrived - i.e datafield 8 is blank in the Delivery table. The link between the tables is Datafield 5 - it has a Material code say “Pump 1”…and that code is in both datafields 5 of the Delivery and Receipt tables. As I say I have 65 tables of 80 datafields each and people want data reported from any and all datafields on all 65 tables. Take the last query example - “and now add the price of Pump 1”…..ah, price is Datafield 4 in the Purchase Table (where Datafield 5 is, again, “Pump1”….
As far as I can see …I do not want 79 lookups from each of the 64 tables in each table…..
MS Access works just fine - if I get the Delivery and Receipt tables in ODBC (normalised without all the arrays stuff of Baserow structure etc) - then I can link by Datafield 5 inner join and lift out any and all datafields I want from either or both the Delivery, Receipt and Purchase Tables as required. ….and if we say Datafield 5 is “10” Pipe” - then I will have MANY records to MANY records…and ACCESS reports it fine. This is the story of the my last 32 years. Surprised Baserow has no ad-hoc report writer, no “across table” virtual tables or Views or materialised Views (made say on the hour).
If you can suggest some techno that can do the above IN Baserow - and without me building redundant tables or every possible combination of 5,000 datafelds - then your my man !!!
Yes - I can “lookup” the 80 datafields of the Catalogue (Datafield 5 “Link to another Table”) in the Delivery and Receipt Tables…but what about the other 63 tables worth of potential reporting people want???!!! (“Give me the arrival Barcode ID now sorted by the Customs Document Number (Datafield 47 in Table 54)”….and so on.
Thanks for your interest and assistance…love Baserow and finding the omission of “bigger system” features I am used to hard to programme around (do not get me started on mandatory datafields….)
Best Regards Mike (in SW London UK)