I have two dbs: Contacts and Donations, both of which are linked by a key field. Contacts is a normal mailing list db with additional fields to record donation information. Donations has fields for various information on the donations such as date given, amount given, and summary figures for each donor such as total, average, count, 12 mo average, etc.
I would like to pull the summary donation information from my Donations db into my Contacts db so that I can display them there.
I have tried lookup( functions, but for them to work, I have to be on the field corresponding to the one in my Donations db. I thought I should be able to create a text array of the key numbers in Donations which contain information I want to pull over into my Contacts db and then somehow loop through those numbers, finding the corresponding record in Contacts and using the lookup( functions. However, I have had no success.
Is there a way using my key field to move the information from four fields in multiple records in my Donations db to corresponding records in my Contacts db with fields having the same names as those in my Donations db?
I am sure that there are several ways of doing this, but here is one. You could have a form with a text list on the left showing the contacts. When you click on a contact, the corresponding donation information would be retrieved and displayed on the right side of the form. Possibly in its own text list, but I don’t know how the Donation database is structured so I don’t know exactly how that would happen. Here’s what I imagine it might look like:
I think you might want to look into the aggregate( function. This could allow you to calculate the totals, counts and averages on the fly, without having to create summary records. You could even calculate a 12-month average by using the queryformula parameter of this function. You could either display this information on a form, or periodically copy it into extra fields in the Contacts database.
I very much appreciate the suggestions from both Tom Cooper and Jim Rea. Those are the things I would like to be able to do, but I’m still learning how to use PanX. Hence, I have several more questions. Any and all help anyone might be able to offer will be received with gratitude.
- My Contacts db has over 3,000 records and my Donations has well over 18,000. The two dbs are keyed on one field. Donations has multiple records corresponding to around 350 records in Contacts. How do I get the figures in Donations corresponding to the given record in Contacts, when there are multiple records in Donations for that individual, i.e., how do I leave Contacts, go to Donations, select the records in Donations corresponding to the original record in Contacts, apply the aggregate( function to obtain a value and then go back to Contacts, paste that value into a field, go back to Donations and selectall, then go back to Contacts to the same record at which I started
- How do I do this process each month to update the figures from Donations for multiple records in Contacts
- I think I can get the 16 figures I would like to have using the aggregate( function as Jim Rea graciously suggested, placing them into fields in Donations, with identically named fields in Contacts. But how can I get the figures from Donations into Contacts. Or do I even need to have these 16 fields in both dbs?
- How could I set up a form with a text list on the left showing the contacts so that when I click on a contact, the corresponding donation information would be retrieved and displayed on the right side of the form, possibly in its own text list, as Tom Cooper has suggested. I have read about text lists, etc., but have no experience setting one up, let alone one which processes and retrieves information from a second db.
THANK YOU in advance!
The aggregate( function can gather information from Donations without leaving Contacts. The fourth parameter is the name of the database containing the data to be aggregated.
This is what the query parameter is for. You write a comparison formula to compare the individuals name, or ID number, or whatever it is that you are using for identification. Only those records that satisfy the condition will be included in the aggregate.
Thank you or this helpful information. However, I’m afraid that I need more instruction in the use of both arrays and the aggregate function (among other things). I’ve read almost everything in the pull-down PanX help files, and watched all the free video training sessions, but there are still big holes in my understanding. Which of the paid video training sessions would be most helpful in filling in those holes? Thanks again.
Will,
I am following up on the suggestion of one approach to have two side-by-side Text Lists. This answers your question #4.
First, I assume you can and have set up the Text List on the left. But if you need more detailed help with that, I or others will be glad to give more details. I assume that the list has Database Navigator checked in the Text List options.
When you click a line in a Text List, the procedure will be triggered. So what I would do, although I am sure there are other ways of accomplishing this, is to have a procedure that builds an array of the donations for the person you just clicked and display that array in the text list on the right. The Text list on the right should NOT have Database Navigator turned on, and the formula will be just a file global variable, for example, it could be fgdonations [I always name file global variable with fg as the start, which helps me keep track of what kind of variable it is]. Back to the procedure on the left. You could have code like this:
let lvidentifier=[Key Field] //Whatever field in Contacts is used to match Donation record.
letfileglobal fgdonations=arraybuild(cr(),"Donations",{Date+tab()+Amount},{[Key Donation Field]=lvidentifier}) //This statement builds the array that is going to be displayed on the right side text list.
showvariables fgdonations //This will cause the text list on the right to refresh with the newly calculated array
Once set up, when you click the name in the list not the left, that person’s donations will appear in the list on the right. Is that what you want to happen? That’s been my assumption.
I hope I am not giving too much or too little info. But keep asking questions if too little.
Tom,
Thank you for your suggestions. From trying to implement what you wrote, I realise that I do need more information about Text List set up. I was able to set up a Text List on the left with various identifier fields including «lastname». That part went fairly well, I think. I was also able to ‘translate’ your code into what would apply to my db (where the key field in both dbs is «unique»):
let lvidentifier = unique
letfileglobal fgdonations=arraybuild(cr(),”Donations”,{Date+tab() + Amount},{unique=lvidentifier})
showvariables fgdonations
But that’s as far as I get. I do not know how to set up the Text List Object on the right which will display the values from my Donations db in the form on my Contacts db.
Using the Construct pull down in my Contacts db only lists fields in that db, NOT those in my Donations db. Plus, I am not sure where I would place the code you graciously provided.
Would you suggest that I purchase some of the Video training modules? In the past, I had manuals to pour over and could ‘practice’ various options until I got it to work. However, with arrays and the newer elements of PanX, I am in well over my head.
Thanks again for your patience and help.
The best way to answer these questions would be with a screen sharing/phone call. Would you like to try that? Tomorrow after 10:30 am EST would be good for me if you want to try that. I can answer all of these questions and you will know how to do it yourself in 30 to 60 minutes.
I don’t know whether to recommend the video modules or not. Once you get the hang of arrays, they are not hard. I took the first PanX course about four years ago. That’s when I learned lots of new stuff about Panorama, including Text Lists. I created the attached sheet to help learn and remember what the various setting do for a text list. It might useful.
If you prefer, I can just write the answer to your questions in a post here; if you don’t want to do a call.
Thank you for the sheet on Text List set up. That is very useful. Thanks too for the offer to get together via phone/zoom/etc. I appreciate the offer, but I’d rather not at this point. There is the time difference and I just want to keep trying on my own to get the hang of some of these concepts. I realise that it is not difficult and that it is probably some small thing that just isn’t clicking as yet. So I’ll keep trying and will probably purchase some of the video training. Thanks again.
Here are some suggestions for you:
-
Test the variable fgdonations to see if it is really doing what you want. Click on a row of the Contacts text list that you set up, which should build an array and assign it to fgdonations with the donation information for that individual. Did it work?
-
To find out, open Formula Workshop (under the Help menu) and type fgdonations. The value of variable should be displayed.
-
To set up a second text list, open the form with the text list you created for the contacts, switch to Graphics mode, and drag a Text List from the Objects Library on the right. In the formula pane, type fgdonations. Make sure Database Navigator is NOT checked.
-
Click the Text List Options icon on the right, then Scroll down in Form Properties to the Multiple Column section. Type in the name of each column in the donation data. That is, if the array has Date and Amount, just type in a list with Date and Amount, so your data will be displayed in columns.
If all of this worked, you have functioning a Text List to display the data for each Contact. There are many refinements that can be added.
Thank you very much for these additional suggestions. I have tried to implement them, but with only negligible success.
- First of all, I do not know where I am to place the code you had previously sent me, which identified the lvidentifier variable. So, that is not in the previous Text List Object which I had created.
- When I go to the Formula Workshop and type in fgdonations = arraybuild(cr(),”Donations”,{Date+tab() + Amount},{unique=lvidentifier}), I get the response: “Field or Variable (fgdonations) does not exist”. – If I type in just arraybuild(cr(),”Donations”,{Date+tab() + Amount},{unique=lvidentifier}) , I get the response: “Field or Variable (Donations) does not exist”.
- I dragged a new Text List Object to the right of the previously created Text List Object. With the new text list clicked, I typed fgdonations into the Formula pane and made sure the Database Navigator was not checked in the Text List Options panel. The following was displayed in the new text list object: “Field or Variable (fgdonations) does not exist”.
- In the Multiple Column Section of the Form Properties panel in the new Text List Object, I typed in @Date and @Amount. That resulted in all the values from those two columns from the Donations db being displayed in the new Text List Object. If I switched back to Graphics mode and clicked in the Text List Object on the left, there was no change in the Text List Object on the right.
So, there is minimal, but basically useless progress. While I do very much appreciate the help I have received from you and others on the Panorama Forum, this is getting quite frustrating. I updated to PanX and extended my subscription with the hope that I could use my old Pan6 dbs with a little tweaking. I have had to put in hours and hours to restructure my dbs (which was probably needed anyway) and I still can’t get the same results as I did with my old Pan6 dbs. The help documentation is not much help, if you are not a programmer or have fairly extensive experience with PanX. That forces people like me to consider paying even more money to get the paid video training in the vain hope that it will be understandable for non-programmers and provide what the help documentation doesn’t.
I think my earlier explanation left something to be desired. Here is jpg file with some hopefully clearer explanation for what I was trying to say. This only deals with your question 1 above. If you get the text list for the Contacts set up correctly, then the next step will be easy.
That won’t work. You should type in the variable that the code created, fgdonations.
Notice that lvidentifier is a local variable, so it will cease to exist when the procedure is completed. Formula Workshop cannot access lvidentifier. The code creates fgdonations as a fileglobal variable, so it will persist as long as that file is open or you destroy it and the Formula Workshop can access it (note also that the in Formula Workshop you set the database that it is referencing.)