[Solved] How to make a report look like an Excel file?

[MTP]

My current company uses Excel to store all handling and processing information for each part number we run. We have Excel sheets on thousands of part numbers. To try to make this data more manageable, I have been doing tutorials and searching the forums, and have used Base to setup a database where I can enter all the handling and processing information for my company's parts. (It is missing some forms and the forms it has are clunky and have macros that don't work like they should... but all the major data holding elements are there.)

I am posting to ask for advice on creating a report. Currently, when my company receives an order, I will look up the Excel worksheet for that part number and print it out. The paper printout travels around our shop with the job, and our workers write down date/time/test results as needed for each handling or processing step. This works very well and I want the transition from Excel to Base to be as seamless as possible.

I have attached a pdf example of how I want the report to look (the current Excel file) and my database. If it helps, this is a summary of my database:

Table ORDERS
OrderNumber (auto-increment, primary key)
PartID (foreign key)
Other information needed about individual orders

Table PARTS
ID (auto-increment, primary key)
RecipeNum (maybe a foreign key? It is 1/2 of a primary key for table RECIPE, see below)
Various columns with other information about the part

Table STEPORDER
PartID (foreign key) (half of the primary key)
StepOrderNum
StepID (foreign key) (the other half of the primary key)

Table STEPS
ID (auto-increment, primary key)
Text of our various handling steps and a filter (StepType) to help the user with data entry

Table RECIPE
RecipeNumber (half of the primary key)
StepNumber (the other half of the primary key)
Text of the time and temperature steps used for high-temperature processing

Tables STEPFILTER and DUMMYORDERTABLE are used to filter records on the forms for entering new part numbers and entering orders, respectively.

The other tables are only used as source material to restrict and standardize data entry on the "PartsData" form.

Ideally I will put a button on the "OrderEntry" form with a macro to launch a report on the currently active row of the ORDERS table that will pull data from ORDERS, PARTS, STEPORDER, STEPS, and RECIPE and look like the attached pdf.

At the top of my report, I want to list some standard information (customer, part number, etc.). In the middle, there is a variable type and number of steps. At the end, there is a standard "accept/reject" block to be filled out by our lab technician, and some standard packing instructions.

I have downloaded Report Builder. It makes reports in Writer, which is a little intimidating to try to make look like a spreadsheet's worksheet; I have not worked with it much so far. I wondered, is there any way to make a report inside Calc that does this? Will the variable type and number of steps in the middle section trip up my making this report?
Any suggestions on what direction to go in to create this report would be much appreciated!

[FJCC]

Calc can be used to make reports. I don't have experience with this, so there may be better methods than what I suggest. Cell ranges can be tied to the results of queries. If your Base file was "registered" when you created it, then you can access it from a Calc document by pressing F4 within Calc. This will open a small window near the top of the document, showing all of your Base documents on the left. By clicking on the plus sign next to a Base document, you can access its tables and queries. You can drag and drop either a query or table into the Calc document. I don't know whether it would be better to use this method to populate your printed form directly or to first place results from the database onto a secondary, non-printing, sheet and use formulas to move that information to the printed form. It doesn't seem like you would want to have a different set of queries for every part number, since there are thousands. Queries can be written to accept a parameter and I expect you would want the query to ask for, say, the recipe and step number. Does that seem like a workable approach?

[MTP]

Creating something in Calc does seem like my best option. Reading through some more threads, I see that Report Builder can't be used with stand-alone forms. I'm wanting the order entry form and the document that gets printed to be outside the Base GUI so it's easier and less intimidating for my co-workers to use.

I can make forms in Calc, with text boxes and combo boxes linked to my databases; however, this seems like it is just "on top" of the spreadsheet since none of the data goes into the actual spreadsheet boxes. This won't work, since I'm going to have a different number of handling and processing steps (so would need different numbers of boxes) for different parts.

If I drag the results of a query into Calc, it just copies the current data and doesn't link anything. How would I get data into a Calc cell and keep it linked to my database?

[Villeroy]

You get a linked import range when you register your database (Tools>Options>Base>Databases), hit F4 and then drag your query from the data source window into the sheet.

[MTP]

I haven't yet developed a query that seems useful for my needs.

So, I tried this with a table. I hit F4 and dragged the table from the data source window into the sheet. I then changed one of the entries, saved the Calc file and closed it. Opened my database, and the entry had not been changed. I changed a different entry in my database, saved and closed it. Opened the Calc file, and saw no change from when I saved the Calc file: it still showed the change I had manually typed that was NOT in the database, and it did not pick up the change I did make in the database.

My conclusion is that the import range was not linked. It was just copied at the time I dragged it. Am I missing an important step?

[FJCC]

Click any cell within the imported data and then select the menu item Data -> Refresh Range. If you want to avoid doing that manually, it is easy to create a macro to do it automatically. The needed lines of code for a single range are here. If you have several ranges, it is easy to build a loop to update them all.

[MTP]

Now I've learned to refresh. (Thank you! Baby steps keep me moving.)

My next question: the refreshable data only appears in a certain format: field names in a row, then data in the row below the field names. I would actually like one piece of data to appear in C1, another piece in I1, O1, G2, etc., and for the field names not to appear at all. (I will make a query that only ever has one row of data.) Any pointers for this action?

[FJCC]

It is not possible, as far as I know, to import the data from Base in the way you want. This is where I would advise using a separate sheet to bring the data in to the Calc document and then use formulas to place the data on your formatted work form. Updating the data range would also result in the formulas updating their values.

[MTP]

That will get me a good start, thanks for the advice.

I have more questions, but I think they are better suited to the Calc or Macros forum, so I'll tag this one as [Solved].