T Howard

A purchase request often has multiple funding lines. That is why I set up the funding table separately. Both the funding table and the vendor history table contain the bud ref field. The bud ref field in the funding table is manually entered. The vendor history table which is a portal on the layout I am using contains information I import from an external accounting system and it reflects all invoices that have been paid against a purchase order number. Each purchase request is assigned a specific purchase order number but this number is assigned after the funding information is provided. The end result I need is for the vendor history portal to only reflect all payments against the assigned purchase order number in the purchase request table and the bud ref years contained in the funding table. Currently, I have the purchase request table and the vendor history table linked with the purchase order field and it works well. But I don't see a workaround for joining the vendor history table and the funding table to each other because both tables are already related to the purchase request table using recordID for the funding table and the purchase order no. field for the vendor history table. I hope this makes sense.

Here is a PDF of the relationship graph. Keep in mind I am a newbie so it may not be organized as well as it should be. OHS_Purchase_Request_Final__OHC1_.pdf

I have a table called "Purchase Requests". It is joined to a table called "Funding" by a field labeled "recordID" which is auto generated serial. The table "Purchase Requests" also is joined to a table called "Vendor History" using a field labeled "Purchase Order No". Both "Vendor History" and the "Funding" table contain a field labeled "Bud Ref". What I need is a portal to show all vendor payments using a match between the "Bud Ref" and "Purchase Order No" fields. Is there a way to do this? Thanks.

Tom, I figured it out. There are two tables involved. I copied the "Funded On" field to the funding detail lines table where the number field being referenced occurs instead of the primary table and it works fine. Thanks again for your help. It was greatly appreciated.

Tom, The script suggestions work well but the auto-enter option doesn't. The field that has the number being entered into it is called "Class Funding" and the field I would like the date entered into is called "Funded On". The field "Funded On" is a timestamp field with auto-enter set to calculation and the parameter is "Case( not IsEmpty(Funding::Class Funding) ; Get ( CurrentHostTimeStamp ) )". Thanks for your help.

Thank you for the information. I have a related question though about how I could have the date inserted into a field based upon a number field having any number being entered into it.

I would like to have a field automatically insert the current date based upon the condition of another field. I have a container field that has a script attached that inserts a persons signature when they click on "yes" from a question that pops up asking "Do you approve?" Yes, No, Cancel. What I would like to do is have a field called "Approved On" and have the date they approve it automatically inserted. Is there a simple way to do this? Any help would be greatly appreciated.

I am trying to create a script that will grab the purchase request number for the current record and insert a link to it in an email that can be clicked on and will take the recipient of the email to that record. The users the email is being sent too all have Filemaker installed and usually leave it open all day. Is this possible? I am using Filemaker 9 Advanced to develop and Filemaker Server 8 for the hosting of the database. Any help would be greatly appreciated.

Thank you for the excellent advice. I did not explore the custom settings under view, create, etc. I had only looked at options under the field access. I tried your suggestion and it works but it brings up another question. How do I eliminate the records that show from the found set on opening of the file.

I have heard setting record level access before but I am still unclear how this works. I know how to restrict access to certain fields with the view, modifiable and no access but how do you restrict access based upon a certain criteria.

I have a database that tracks purchase orders, budgets,etc. I am wanting to restrict access to certain records. If a user is a division director, I want them to be able to see all purchase orders created by anyone in their department. If a user is not a division director, then they only see records they have created. I set this script up to run upon the file being opened. The globals referenced are calculated unstored fields. I am not sure whether to have them as global auto-entered calc fields or as global text fields and have the information set by the script. The script is set to run with full access privileges. I have not been able to make it work. Any suggestions would be greatfully appreciated. Set Field [ purchase requests::gAccountName; Get(AccountName) ] Set Field [ purchase requests::gDivisionName; authorized users::User_Division ] Set Field [ purchase requests::gPrivilegeSet; Get(PrivilegeSetName) ] Go to Layout [ “PR Input Layout” (purchase requests) ] Enter Find Mode [ ] If [ purchase requests::gPrivilegeSet = "Division Directors" or "Empl w Division Director Auth" ] Insert Calculated Result [ purchase requests::Division; purchase requests::gDivisionName ] [ Select ] Perform Find [ ] Else If [ purchase requests::gPrivilegeSet = "Users" ] Insert Calculated Result [ purchase requests::Created By; Get(AccountName) ] [ Select ] Perform Find [ ] End If

It is partly working now. I used a startup script that set the global text field gSetPrivilegeName upon the file opening but it retains the privilege set name previously set when I logon as a different user.

I am using a database that restricts access to certain records. I use the Get(AccountName) in a global field and I am trying to use the Get(PrivilegeSetName) in a global field but nothing is returned for the value. The other function Get(AccountName) works fine and I mirrored the privilege set function the same. Please tell me what I am missing.

Vaughan, Thanks you for your suggestion and it worked great. I wrote my first response before trying your suggestion. At first, I didn't think it would work but it did. Thanks again.

Vaughan, Can you expand on the list suggestion? I need several fields such as fiscal year, location, funding source etc. to be in the header or only listed once on the page and the other fields (gl code, gl descr, amount, and projects to be listed on each line of data that was entered. Thank you for responding.

Two of the tables I have in my database are a Budgets table and a Budget Line Details table. They are related by three fields, Fiscal Year, Location, and Funding Source. I have a layout setup with Budgets fields above a portal to the Budget Line Details table. Everything works and looks fine on the data entry layout. What I need to is setup a print layout that will let a certain field on the line detail portal called Projects expand or shrink with what is entered. I tried setting up a layout with no portal and repeating fields for the detail portion but I only get one line of detail for each record. What am I doing wrong? I understand you can't make portals shrink fields so I can't use that method to print. I also want the result to break across pages if the lines won't fit on one.

OK just put DUH on my forehead. I created a calculated sum field like you mentioned in the PO table and referenced the amount in the payments table. Before I was using the summary of amount field from the payments table in my balance equation. Using the calculated field from the PO table works and updates the total after changing the PO number. Thank you.

I have tried that but it only gives me the first amount listed in the portal and not the sum of all.

I have a portal on my Purchase Order layout that shows all payments based upon the purchase order number on the main layout. The payment history table and the PO table are linked by the PO #. I created a summary that shows the PO total less payments = balance. The problem is the payments total that is a summary field in the payments table won't update when the PO number is changed unless you click in the field and then out of it. Please help.

Thank you Malagasy and everyone else in this post. My script is now working and I incorporated some of the suggestions regarding global fields.

Malagasy, Can you please post the final version of your script that worked? I have tried piecing the posts together for a solution but nothing works.

I am trying to write a script that will insert a scanned signature graphic into a container field once a user selects the container field. I need it to ask "Do you approve? Y or N" and then if the answer is Y then insert graphic. If N end script. I am using a user table that is related to account name which is required to login to the database. The user table has Account Name, Title and Scanned Signature fields. Please help.