Transposing a Report

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I have a report that shows 4 fields for each of 50 items for approximately 500 stores. It is very difficult to have to scroll over to see all of this data. I see that someone posted an option to transpose the report with _.transpose(qdb_data), but I am an app developer newbie. Can anyone explain in dummy terms how to go about setting this up?

I've attached samples of the regular report and the transposed version.

Thank you

Kim

Photo of Kim

Kim

  • 142 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb
Hi Kim,

I'm going to guess that this is a "summary" type report.

It looks like the rows are set up as store #.

Can you post a screen-shot of the report details "Summarize" and "Grouping and crosstabs"?

Is it the case that API1, API2, API3...  is 1 to 50?

And for each API, you have the fields:
AP1 External
AP1 New or Existing
AP1 Installed S/N
AP1 Installed MAC

It seems that these my be set up as "Group By Columns"

I'm guessing too much.  A screen shot would be very useful.

Thanks,

David in Tucson
Photo of Kim

Kim

  • 142 Points 100 badge 2x thumb
Hi David, this is actually a detail report. I have not grouped or summarized. It is just sorted by store no.

Each store physically has 50 APs: AP1, AP2, AP3, etc. Each of those 50 APs has the 4 fields you listed above, but there is no actual field in QuickBase called AP1, AP2, etc. Just the fields below:

AP1 External
AP1 New or Existing
AP1 Installed S/N
AP1 Installed MAC
AP2 External
AP2 New or Existing
AP2 Installed S/N
AP2 Installed MAC
AP3 External
AP3 New or Existing
AP3 Installed S/N
AP3 Installed MAC etc...

So, basically the report currently shows the data like this:

Store  AP1 External  AP1 New or Existing  AP1 Installed S/N  AP1 Installed Mac  AP2 External  AP2 New or Existing ...
1         n                      New                            123123                   123abc                   y                      Existing
2         y                      Existing                       456456                   456abc                   n                      New

I need to show it like this:

Store                                  1               2              3                4               5
AP1 External                     n              y
AP1 New or Existing         New        Existing
AP1 Installed S/N             123123    456456
AP1 Installed MAC           123abc   456abc
AP2 External                     y              n
AP2 New or Existing         Existing   New

I'm wondering if perhaps the issue is with the way I set up the fields. This is my first time creating an app and I don't have experience in creating databases, so my apologies if that is the issue.

Thank you,

Kim
Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb
Hi Kim,

From what you described in your example above, it sounds like you are thinking that
showing the 4 detail fields horizontally for all 500 stores would be better.  Is that right?

I have an idea that may prove useful and more compact.

Since all the data is in one record for each store, the way you are seeing it is a reflection of how it is stored.

If you are early enough in your process, or otherwise have flexibility to change your layout, you may find that creating a record for each set of AP data for each store may be a good way to go.

If your app is established, you may want to make a backup copy and experiment in the copy so that if your changes / experiments do not work out as planned, you don't have a problem for anyone else.  (It is also handy to have a practice App to experiment in.)

I would:

Add a new field called AP#, let it be a multiple-choice text field with values 1 to 50.

(This is where seeing your full layout would help, I'm guessing based only on the fields I know about.)


Adjust your data so that each record has the store specific data and the AP specific fields

    AP #  (New, multiple choice, 1-50)
    External
    New or Existing
    Installed S/N 
    Installed MAC

Plus any comments or additional information.

Each record will have store data & detail for one AP#.

Your report can be a table report Sorted and Grouped by Store, displaying the AP data.  (You can set up the report so that there is not a Store column, and allow the Sort & Group feature to display the Store.)


You can make a version that has a filter it to ask the user which store to display (_ask1_ in the filter criteria for the store field will prompt you at run time.)
You can also adjust the dynamic filters to include store and select specific stores from the full, un-filtered report.

If you are feeling adventurous and have a bit of time, you could create a second table and  a relationship to have the AP# data in the second table linked to the main store record. This would end up with 50 AP# detail records linked to each store record.

This section in the QuickBase help describes table-to-table relationships
http://www.quickbase.com/user-assistance/Default.html#about_relationships.html

I trust this is useful.

- David in Tucson
Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb
Kim, was this helpful?
Photo of Kim

Kim

  • 142 Points 100 badge 2x thumb
Yes. Thank you so much, David. I have been looking for a  better way to set up these fields, so you have helped me with two questions!
Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb
I'm glad.

- David
Photo of Kim

Kim

  • 142 Points 100 badge 2x thumb
Hi David,

I just want to say thanks again for your help.

I knew there was something wrong when I was setting up those fields initially, but I didn't know how to do it correctly. You have shown me the way! I'm now creating a new table whenever there is more than one of an item per store. The links in my reports and forms are appearing automatically and everything is working perfectly.

Thanks again and have a great day!

Kim
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
FWIW, there are ways to technically transpose a table but I hesitated to post an answer because you are not going to get the exact formatting and behavior you might expect. For example the header display, sorting and freezing features in native QuickBasse reports are not easily reproduced if you transpose a table. HTML tables were designed largely for vertical display and probably assumed a desktop based browser where scrolling is vertical.  The situation is also complicated on tablets where scrolling is more naturally horizontal rather than vertical. But the fact that you are asking for this transpose feature is probably a "code smell" that you application should have its database schema modified as David alludes to.

BTW, did you know that individual lobsters are left- or right-handed? Their crusher claw is their dominant one and then they have a pincer claw. See: http://www.brantfordexpositor.ca/2012/05/09/a-left-handed-lobster-who-knew
Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb
I'm glad that what I shared help.  Thanks for taking the time to let me know.
- David