New to Forum...Need Help With My Layout Please

[AzNsUpRa]

Hello. I am new to this forum, and I have a question about FMPRO 6. Currently, I have repeating fields setup where I populate all of my data. However, sometimes not all the fields will be used. I want to make a layout where it will check the value of a checkbox field. If the box is unchecked I want the data across that row to show up on my report. If the box is checked I don't want the data to show up.

Also, sometimes I will have all 10 rows populated, but some of those fields will not need to show up. Thus, I don't need to have all 10 of the rows in my layout taking up space (prepping for the worst possible case). I need it to be dynamic, and depending on how many boxes are unchecked that's how many rows I need to display on my report. Basically, I need to know how to make repeated fields show up in a layout based on the data inside a checkbox repeated field. I have not had any luck making a script for this. Any help would be appreciated. Thanks.

[Vaughan]

You need to get rid of the repeating fields and use a relational database structure. A bit of work but you'll never regret the change.

[AzNsUpRa]

I dont' mind getting rid of the repeating fields and setting up a relational database. However, back to the original question how would I set up the fields in the relational database ? Is there some way to store the data without using repeating fields ? If so then how ? I just want to be able to check a checkbox field and if it unchecked show all the data that is linked to that check box field in a layout. Thanks for your help. It is truly appreciated.

[Fitch]

Here's how you might approach this. First, make sure that each record in your db "A" has a unique ID or serial number -- I would hope you already do. Now, make a clone of A, let's call the clone "B." Then import all your records into B. It will ask you if you want to split repeating fields into separate records. Say yes. Once the data is imported, you can set your fields in B to be non-repeating, and you can delete them from A.

You now have all kinds of options. Make a relationship in A to B based on the ID, and you can then display the related records in a portal. For printing, you may print from A using portals, or you may want to print from B, which will give you the most flexibility with sliding up/reducing the size of the enclosing part, and with sorting/subsummaries.

To show only selected related records: if you have a checkbox in B with a value list of 1, you can make a calculated field that is Case(checkbox, ID). Then you can make a relationship in A that is ID::checkboxCalcID and that will show only the checked related records in a portal that uses that relationship.

[AzNsUpRa]

Thanks for the help Fitch. I will give it a try and let you know how it goes.

[AzNsUpRa]

Well...it kinda works now. I tried what you told me to do, and I made a clone and imported the records. I have gotten rid of the repeating fields in DB "A", and now I have a bunch of records with the same "ID" in my DB "B". I made a relationship between A and B using the ID.

However, back to the checkbox problem, when I try to make a calculated field that is Case(checkbox, ID) and I try to display this in a portal format using the ID::checkboxCalcID relationship in DB A nothing shows up.

When I use the relationship ID::ID, everything shows up, so I guess the calculation field is wrong. The name of my checkbox field is "Issue Resolution" and my value list contains the value "Resolved." I need to display the records where the checkbox is not checked. I thought something like Case(Issue Resolution != Resolved, ID) would work, but it doesn't. Any help is greatly appreciated. Thanks.

[AzNsUpRa]

I guess I figured out what the problem is. My ID in DB "A" is an Indexed, Auto-enter Serial number format. An example is "TR03-302". When I use the Case function in my DB "B", it returns only 3302. They don't match up, and that's why it's not showing up in my portal in DB "A". Is there a way to fix this :

[RalphL]

Make the serial numbers in both tables text fields.

[AzNsUpRa]

Tried that, but the data repeats itself in the portal. There are a right number of rows in the portal, but the rows are just duplicates of the first row.

[-Queue-]

Check that the field in your portal uses the same relationship as the portal. When portal rows duplicate, the relationships are usually different.

[bruceR]

To be more specific, if the portal is "Line Items" then the fields you place in it should be Line Items::Part Number, Line Items::Quantity, etc. You see this when you double click on the field or use the menu Format: Specify Field.

[AzNsUpRa]

Thanks for the help everybody. I was able to get it to work. All your help is much appreciated, and thanks again.