Displaying SQL result in table form (mimicking a portal)

[rkass068]

Hello,

 

I am currently running a report based on a certain table in my DB that has an SQL function pulling data from another table. The problem is the report will not function properly with a portal since i would have to do some complicated relationship maps. So I decided to use SQL to pull data from the other table and it works great. Its pulling the correct data.

 

My issue is in how it displays the data. The data that is pulled includes the fields 

 

Date   Day    Day of the week and Description

 

Those would be the header titles and I want the data to be displayed in a table format. I cannot find how to do that and I noticed a program called SQL explorer that displays the data in a table form. The way it is set up it totally over my head and I was wondering if anyone has an explanation how I can do this for my database solution.

 

 

Thank you

[the Otter]

Is this data you’ll only want to update when you run the report, or does it have to be live? I can think of several ways to accomplish this, but which is most helpful would probably be heavily influenced by exactly how you’re using the data.

[rkass068]

Hello

 

Thanks for the response. I would need the data to be displayed only when i print a report. So right now the sql calculation pulls all of the info but it displayed the data unevenly to make a nice looking report. I would like to space out the data and add lines to make it look kind of like an excel spreadsheet where the data is evenly spaced out. Also The field "description" can be long in text and I was wondering if the table could show all of the text and not cut off if its too long. 

 

Thanks

[the Otter]

Depending on how much data we’re talking about, a good solution might be to place the primary keys from the SQL data in a variable and create calculation fields to display the data you want. Here’s an example:

 

Field 1:

sqlKey (unstored):

GetValue ( $$SQLKeys ; get(RecordNumber) ) 

Field 2:

date:

ExecuteSQL (

   "SELECT " & quote ( "Date" ) & " " &
   "FROM OtherTableName " &
   "WHERE keyField = ?"

   ; $null ; $null ; sqlKey

) //end ExecuteSQL

The first field will include the first key value from the SQL table in the first record, the second key value in the second, and so on. The fields you want to print can be created per the instructions for Field 2. Write the report script to display the same number of records as exist in the SQL data (using New Record or Omit Multiple Records, depending on whether or not the table already has enough records) and you’re good!

[rkass068]

I'll try this out. Will I need a field for every field referred by the SQL table? For example I want to display the fields in the following order in table format:

 

 

       Date            Day       Day of Week     Description

ex.   10-mar-03   1          Tuesday             Given 10mg of cyclophosphamide............ etc

        .....               ...           ......                   ....

 

Lets assume the amount of rows are infinite

[comment]

If I understand correctly the process you describe, you start by pulling selected records into a text table - and then you look for a real table to display them.

 

Why don't you simply perform a find in the table where the original records are, instead of using SQL. Then you would already have a record for each row, and all you'd need is a layout to print them.

 

 

---

P.S. You titled this "mimicking a portal" - but a portal would not serve you well here, because (1) portals do not print well across page breaks, and (2) all portal rows have the same height when printing.

Edited October 15, 2014 by comment

[rkass068]

P.S. You titled this "mimicking a portal" - but a portal would not serve you well here, because (1) portals do not print well across page breaks, and (2) all portal rows have the same height when printing.

 

Yes thats is why I said "mimicking". Okay let me explain my database solution fully and how a find won't work for me. 

 

My database has the tables:

 

1. Patients

2. BMT (bone marrow transplants)

3. Collections

4. Protocols (High dose chemotherapies given before a collection)

 

Now 1 patient can have multiple BMTs. Each BMT have multiple collection and multiple protocols.

 

Relationships:

 

Patients: pk_patients-----> BMT: fk_patients

 

BMT: pk_BMT----->Collections: fk_BMT

                      ------> Protocols: fk_BMT

 

Report:

 

In the report, The body must display all of the protocols for a given BMT, which is very easy. By doing this I can make the protocols look in table form. 

 

Day    Date     Dayofweek    Description. 

 

The description field can be very long so in the report I have "sliding based upon above" and "resize enclosed part" This way it looks more like  table with sometimes having the description field larger when its printed. 

 

It all falls apart when i need to display the collection. If a BMT requires 2 days of collections for example, there will be one BMT record, multiple protocol records and 2 collection records. I want the footer or sub footer to display either the 1st collection or 2nd collection record depending on which day I need to print the report for. 

 

This is possible if I make the layout linked to the collections table but then I will not be able to display the protocols properly without using a portal or SQL.

 

This was entirely possible with access (the old DB that is now crashing like crazy) I just don't understand why its harder with filemaker. 

 

thanks

[Kris M]

Do a google search for FileMaker Virtual List