Only show certain records from Portal

[LA Girl]

I have a job record with a portal revealing the perfomers who worked on the job. Some of the perfomers will not get paid ontime and will have a late payment, which I have fields for on the portal calculating the amount due. In another file I have the late payment letters. I'd like ONLY the perfomers who have late payments to show up on the portal in the late payment letters file. How can I just choose those people and not have everyone show up from the original portal?

[QuinTech]

Hi Girl, and welcome to FM Forums! Try this: Make a calc field in the Performers file called WillBePaidLate. The calc for this could be (AmountDue <> 0), which will always resolve to 1 (true) if there is an amount due. You could construct this calc another way; really, whatever is convient. The important thing is that it be equal to 1 whenever there is a late payment.

Now in your Letters file create a calc field called AlwaysOne that is equal to, simply, the number 1. In both files, create a new calc field that combines the existing key with the field just created; so you'd have, for example, in Letters:

Calc = PerformerID & "|" & AlwaysOne

(The character between the ampersands above is the pipe character, Shift-, and is frequently used as a delimiter in key fields like this one.)

Now make your relationship between these new calc fields. It should only return records for which payment is late.

HTH,

Jerry

[LA Girl]

Thank you sooo much for your input... just wondering if the "AlwaysOne" field is put on the line items of my portal in my letters file, or on the record itself? I'm sure I've made this way more complicated than need be. I actually have three separate letter files each relating to the main address advertisor file (which brings over the address info, etc.) and then to the Session Report file( which has my talent portal and session info). I have littel scripts that copy the key to the advertisor file and then the session key and open my letter file, create new record and paste they keys to bring the info over. I'm sure there's a better way but it's actually working ok. Before your suggestion I created a field on my portal in the Session file that lets me check box either for late, penalty, or adjustment. Then, I'd thought if it said late for that person i could somehow link that to the portal in late letters. And if it said penalty, it would show up to that letter portal, and if it said both I could make a letter in each file and that person would show up. Or, if it's better could I do three separate checkbox fields with value of "1" and then follow your example and put the one field in each letter file? Or just forget my idea and do yours. So, close but so far!

[QuinTech]

You won't actually need to display the "AlwaysOne" field anywhere. It is there just for coding.

I actually have three separate letter files each relating to the main address advertisor file (which brings over the address info, etc.) and then to the Session Report file( which has my talent portal and session info).

This is certainly not more complicated than it needs to be. In fact, it's good relational database design to separate different types of objects (such as advertisers, sessions, performers, and letters) into different files. It is not necessarily true that you need a separate file for each letter, but your situation may warrant it, so if it's working for you, who's to complain?

I have littel scripts that copy the key to the advertisor file and then the session key and open my letter file, create new record and paste they keys to bring the info over. I'm sure there's a better way but it's actually working ok.

Again, if it works, you certainly don't need to fix it. A script is not the easiest way to do this; look into the "Allow creation of new records" option in the relationship. This allows you to create a new record in a portal just by typing data into the portal. You may find this works better for you.

I created a field on my portal in the Session file that lets me check box either for late, penalty, or adjustment. ... Or, if it's better could I do three separate checkbox fields with value of "1" and then follow your example and put the one field in each letter file?

Let's combine ideas. Keep your checkbox as it is, in one field. But where i suggested you make a calc field called WillBePaidLate -- do that, but also make two other calc fields, IsPenalty and IsAdjustment, as follows:

IsLate              = ( yourCheckboxField = "Late" )

IsPenalty         = ( yourCheckboxField = "Penalty" )

IsAdjustment = ( yourCheckboxField = "Adjustment" )

Incidentally, the statement ( yourCheckboxField = "Late" ) is functionally equivalent to

---

If ( yourCheckboxField = "Late" , 1 , 0 )

---

which is functionally equivalent to (and has slower performance than)

---

Case ( yourCheckboxField = "Late" , 1 )

---

as -Queue- would want me to point out. (Hi, JT! )

J

[-Queue-]

Preach it, Jerry! <chant> Jerry! Jerry! Jerry! </chant>

Hmm, where have I heard that before?

[LA Girl]

Ugh! I'm all a mess here, Jerry! Ok... so I created the 3 new "Is" fields like you said - one on each letter record (not in a portal, just on the overall record for the letter data entry layout). Then, I created an Always One calculation field that is equal to 1, like you said. I put the IsLate field on my line in the portal and it does show up "1" (I checked the late box for the first two people on the portal in the primary file), as does the combo field that I put above the portal at the top of my data entry screen. Then I created a relationship between my Combo field and IsLate(for the late letter file). This relationship I applied to my portal on the letter. So, now my portal on my letter is repeating the first line of the other portal 7 times. Like I said, I have a relationship for fields I have above the portal (like date of service, production co, etc). Mabye this is causing problems?

[-Queue-]

Row repetition in a portal is usually due to the fields within it using a different relationship than the portal. Did you double-check your fields?

[LA Girl]

Well, that does fix that problem but now it's showing up everyone who is late on all records in the Session Reports (I checked a couple more boxes on other record's portals as well). We're almost there! I have to go now so, I'll have to return on Monday to fixed this. Thanks sooooo much for the help. I can't wait to get this working to make my life a bit easier. Have a great weekend!

[-Queue-]

I'm thinking you'll need the Is_ calculations to be in the related file, not the file in which you're viewing the portal. That way each performer's record determines whether it belongs in the related portal, whereas you seem to have it set so that the letter record determines whether all or no records are related.

[QuinTech]

-Queue- has got it. See the attached for an example.

LAGirl.zip

[LA Girl]

Ok, I'm back. You guys are great!! But, I actually DON'T have the Is field on the letter record... just the Always One field at the top of record (not in a portal). I think the problem is here: The performers are related to certain jobs. Let me regroup and start over in explaining.

Here's what I'm trying to do:

I have a Job Report file. A record here reflects a certain job. At the top I have company info and job info. Below that I have a portal (which relates to a Line Items file)that contains all the performers who worked that job. Say checks come in on time for all performers, except for one is paid late. I check that Is Late box on the portal line for that performer. Then I click a button and it creates a letter in the Late Letter file for that job and on that record there is a portal that contains the name of that one performer and the amounts which are due for the late fee.

[LA Girl]

Where are y'all?? Do I need a global field or something? So, basically my portal needs to be based on two criteria - coming from a particular job and needing a particular letter. Help please

[QuinTech]

Whoops, sorry, missed this the first time around.

But, I actually DON'T have the Is field on the letter record... just the Always One field at the top of record (not in a portal).

None of these fields should be displayed anywhere. The end user should have no knowledge of them at all. You can set and use their values completely behind the scenes.

I have a Job Report file. A record here reflects a certain job. At the top I have company info and job info. Below that I have a portal (which relates to a Line Items file)that contains all the performers who worked that job. Say checks come in on time for all performers, except for one is paid late. I check that Is Late box

AAAANNNNHHHH!! (Sorry, that's the best i could do imitating that nasty "wrong answer" noise from Family Feud.) You had it right the first time, with the checkboxes. All three options should be in this checkbox (Late, Penalty, Adjustment). When you check "Late" in that box, a separate, calculated number, undisplayed field called "IsLate" resolves to 1.

Now, moving over to the LateLetter file, you have an undisplayed, calculated number field called "AlwaysOne" that, surprisingly, always resolves to 1. Make a relationship between this field and IsLate in the Performers file. Now any portal based on that relationship will always show only those performers who have a check in the "Late" box in that field on the Performers file.

It's up to you what to do with that; my suggestion would be to have a button in that portal that triggers a script that sets merge fields to the performer's name. For example, the LateLetter file has a field called "ToName", and your script sets ToName to Performers::FirstName or something like that. Be sure that this Set Field step is the first step in your script; otherwise Filemaker may lose focus on the correct portal row, in which case it will just return data for the first performer in the list.

J

[LA Girl]

I'm confused J... I did a "Combo" field (like you said at first) for the key between the two files (the job report and the late letter). That works ok except that it lists every perfomer with a late check from every job record, not just the one I need the letter for. I don't have an individual performer file (just line items file of performers for the job report file). Individual performers will be on multiple job report records. I just changed the key to the Always One in the letter instead of Combo and the result was for the one record I was looking at, one checked performer was the only one listed and it was listed like 8 times in the portal and showed up on every letter. I don't really want a button script per portal line because I suppose that say I have a job and it has 10 late people, then I would have to click each one separately to move each name over to the letter file? What I need is the portal on the letter to reflect the names of only the people that worked on the job that also were late (or had additional monies owed, etc).

[QuinTech]

Can you post your files here, stripped of data if necessary? I think that would help me a lot.

J

[LA Girl]

I'm tyring to attach the files but it keeps saying that i have an ext. name on end of file name and it won't let me. I don't have any extension added to the name.

[QuinTech]

Try compressing them with WinZip or another compression utility first. FM Forums won't allow you to attach Filemaker files directly.

[LA Girl]

Ok, I've attached a few files from the whole database. You'll just need to take the clone part off the name for relationships to work. I think these will do to help you. The letter file that I actually started trying to link is called "Liquidated Damages", not Late Letter (it was easier to type Late so I used it as example). Anyway, the Late Letter I included for you because I was trying to get the merge fields to work on my letter. By work , I mean that I couldn't get the sliding to slide the address 2 field up when it was empty. Actually, I couldn't get the regular fields to do that either. Something else to play with if you want. Anyway, the "Contacts" is just the file where the company addresses come from but that's pretty much all that file is needed for here. The MReports file is really where all the action starts from as far as creating a job and then creating a letter from here. I don't have a button for Creating Liquidated Letter so, just use the script pull down and select that one. Let me know if you have any questions. Thanks MUCHO!

LAGIRLFM.zip

[QuinTech]

Thanks, i'll take a look at them tomorrow probably.

[QuinTech]

First off... i gave you the wrong calculation for the IsLate field. It should be

PatternCount ( Claims , "Late" )

That way, if there is more than one option checked off, the field will be true if "Late" appears anywhere in Claims. Likewise for IsPenalty and IsSecurity.

Second... if you want the ability to mass-print late letters, print from the MR Line Items file. Have a relationship there that is the converse of the existing one from Liquidated Claims to MR Line Items. Edit the letter text in Liquidated Claims, but print from MR Line Items. There is an example as the last layout in that file. If you are going to have the same text for each letter, you can use that layout largely as is. Scipt #4 in MR Line Items (PRINT LATE LETTERS) will show you how i'd do this.

3. Don't forget to close merge fields with >>.

4. You'd probably be better off with one letters file, each one having a LetterID, and linking to the appropriate letter through a concatenated key.

I think this is a slightly different model than the one you've had in mind, so try to step back and take a look at it before getting into it. Letters are really created from the MR Line Items file under my proposed model, which i think is more appropriate because though several letters may share the same text or other elements, they are really different objects, and each has a closer relationship with the performer than it does with the text/body of the letter.

J

lagirlMOD.zip

[LA Girl]

Thanks J, I'll have to look at this next week. I had way too much work today to play with it. Have great weekend!

[LA Girl]

no, it still doesn't work. If you add another record in yours, and then look at the letter, all the people from all the records show up on the letter. I need it to be specific to the job. So, only those on one job showing up on one letter. Does that make sense?

[QuinTech]

Take a look at the attached file for an example of how to do this with concatenated keys. Especially notice the two new fields, Liquidated Claims::Always1|JobNo and MR Line Items::IsSecurity|JobNo.

J

lagirl2.zip

[LA Girl]

Thanks J, we're getting there. My "job number" is actually the Record No. in the Reports file. I guess I could add a new field to the portal and retype this number, but wondering if there's another way. Then, I'm guessing I redue my create letters script to cut and paste the record number in the JobNo. global field on the letters? Not sure that the global job number field will work in the letters file though. If you create another letter and change the job number then all the letters reflect the same person instead of the people that are supposed to be there on each letter.

[QuinTech]

My "job number" is actually the Record No. in the Reports file.

In that case, do not create a field named JobNo, just use Record No. as i suggested you use JobNo. This means you will not have to enter any data into a global, the records will be displayed dynamically for each record.

J

[LA Girl]

Oh, so close! Ok, I fixed what you said but I already notice a problem. Here's an example...

I have two job numbers ( record numbers) - one is 61 and one is 161. I have one person on each record, so I have two records in MR Line Items. Lets say one is Joe Blow (61) and one is Jane Doe (161). I create a Security Letter for Joe, so his IsSecurity|JobNo field is 1|61. Jane Doe does not have a letter so, here number is 0|161. Her's shows up on Joe's letter. BUT, if I check her security box and her number changes to 1|161, then she doesn't show up. Any ideas?

[Ender]

Make sure the key fields are 'Text' results, and that the separator is a pipe character:

IsSecurity & "|" & JobNo

[LA Girl]

Finally, had time to fix this thing. THank you sooo much. You guys are AWESOME!!! It works great! Only thing is I can't get my merge fields to work ( to slide up when there is a blank address line).

[-Queue-]

Are the merge fields in a single text block or individual text blocks?

[-Queue-]

I guess you answered my question in the other post. Please don't double post though. I'll be deleting that thread since it mirrors this one.

Your answer is to put the merge fields in the same text block, so that they act as a unit. Grouping them only helps formatting or moving them around in Layout Mode. It doesn't impact how they work in other modes.

[LA Girl]

Works! Thanks!!