Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Display 2 relational fields as lists on a layout


bubba667
 Share

Recommended Posts

Sup guys,

 

I am trying to display 2 relational fields from a table in a list like layout (the relational fields take as much space as needed). I understand how to make a list view to display 1 relational field, but I am having a hard time finding a way to do this for 2 related fields.

 

The tables are:

- Main table

- Secondary table1

- Secondary table2

 

All 3 tables are related using the same 'cust_ID' field.

 

The objective of this layout is to print a report.

 

Tx in advance!

Link to comment
Share on other sites

Your question could be interpreted in a number of ways, each needing a different type of solution. It would help if you could clarify what the tables are and post an example of the output that you'd like to see (specifying which table each field is from).

Link to comment
Share on other sites

We are a security systems compagny. The main table contains customer information, the related tables contain zones and contact numbers.

 

Main table:

- Cust_ID

- Name

- Adress...

 

Zones:

- Cust_ID

- Zone #

- Zone location

 

Contacts:

- Cust_ID

- Contact Name

- Phone1

- Phone2

 

Zones and contacts are related fields because each of them can range from 1 to 100.

 

The result we are looking for looks like this:

 

Our compagny information

Clients information

List of Zones

List of contacts

 

I hope that clarifies my question :)

Link to comment
Share on other sites

A standard report can only deal with one related table at a time (you put the "report" in the related table as a list view with the original parent table fields at the top in the header). So you want a nonstandard-report approach.

 

Here's one:

 

Define a calc field in the table containing the Zone data, call it "Zonestring", formula = Zone #&" "&Zone Information. (Actually, would zone information ever contain hard returns? If so, substitute them out in this field, e.g, make ZoneString = Zone # & Substitute(Zone Information, "¶", " ")

 

Define a Value List, "ZoneData", consisting of values of field "Zonestring", set up to show only related values starting from Main table.

 

Create a text calc field in Main table, defined as ValueListItems(Get(FileName), "ZoneData"), call it "ZoneData4Report".

 

Create your report in layout mode on a Main Table layout, as a form layout. Put all the customer identification fields first, then where you want the zone data to appear, type this literal string on the layout:

 

>

 

In browse mode or preview mode, that will be replaced with the contents of the field, it's a merge field code. Now single-click on it and you'll see four text object drag handles. Drag one of the bottommost ones downscreen until the text object takes up far more vertical room than you'd ever need for the zone info of a single client. Also make the text object wide enough to accomodate zone info. Then, while the text object is still highlighted, to to the Format menu, pick "Sliding/Printing", and set it to slide upwards and also to reduce the size of the enclosing part.

 

Any field or other object you put on the layout below this will also need to be selected and the same "Sliding/Printing" option set for it so it will also slide up to take up any empty space.

 

Now repeat the process for ContactInfo, using a concatenated calc field for Contact Name & Phone 1 and Phone 2, putting its textobject below the ZoneInfo4Report textobject, also sliding up / reducing enclosing part.

 

When you view this screen in preview mode, or print it out, you'll get what looks like two separate list views, each taking up only as much room as they need to display/print the data.

Link to comment
Share on other sites

I did what AHunter3 suggested. It worked perfectly with 1 minor defect. The string Contact Name&" "&Phone 1&" "&Phone 2 is not aligned when displayed. I tried inserting "\t" to delimit a tab with no success. Therefore my last question for this thread is how to insert tab spacing in the calculation field? :P

 

 

Tx

Link to comment
Share on other sites

I tried what comment said, and while it is adding a tab, the data doesnt align properly. For example:

 

 

Mr.A 123-456-7890

Mr.longername 123-456-7890

Mr.B 123-456-7890

 

I tried adding 2 tabs hoping it would fix this problem but it still persists. Any workarounds to obtain an aligned output? For example:

 

Mr.A 123-456-7890

Mr.Longername 123-456-7890

Mr.B 123-456-7890

 

 

Thanks

Link to comment
Share on other sites

hmm..cant seem to be able to edit my msg, the output of the last example is not right. I want the phone numbers to be left-aligned. Hope that clarifies.

Link to comment
Share on other sites

In Layout Mode, double-click the text object. (It should "open up" with a blinking text cursor, ready for you to edit the text). Go to the View menu and turn on the Text Ruler. You'll get a little text ruler the width of the text object, with increments (inches or other units). Click into the ruler itself and you'll find that you can set the position of tab stops. If none are set, FileMaker uses some default horizontal distance per tab, but with this tool you can set the tab far enough to the right that Mr. Longername doesn't end up wtih his phone number a full tab stop farther to the right than the other entries. (Of course you may have to come back and readjust after someone inputs an entry for Mister Hyphenated-Ludicrouslylongname ;))

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use