…pulling P1Skills into the search database from the tracker database. However, what I really want to do is a bit more complicated.
In the search database, there is a field called Order, and the value of Order can be P1 or P2. What I’d like to say is “If the Order field matches P1, get P1Skills, else P2Skills.”
You could retrieve Order, P1 and P2 from the search database, then decide based on Order whether to use P1 or P2. I would also look at superlookup to see if that could do this.
Thanks, @CooperT - being relatively new to the program (and programming) I tried to implement your suggestion as follows, but I’m doing something wrong:
if Order = “P1”
field Skills
formulafill lookup(“PATrackerDatabase2020”,«RecordNumber»,«RecordNumber»,«P1Skills»)
endif
if Order = “P2”
field Skills
formulafill lookup(“PATrackerDatabase2020”,«RecordNumber»,«RecordNumber»,«P2Skills»)
endif
I’m sure I screwed that syntax up Right now it only prints P1Skills to every record. Thank you for any thoughts on how to fix my mistake and implement your suggestion correctly.
No, sorry if I explained that wrong. Order is in the search database. It’s so that I can notate who the school lists as parent 1 and 2. So I’m using that field to decide what to pull from PATrackerDatabase2020, which has both parents in one record. Sorry if I explained that wrong. Thank you so much for your responses. Hope this cleared it up.
I think what you want is:
formulafill ?(lookup(“PATrackerDatabase2020”,«RecordNumber»,«RecordNumber»,Order)=“P1”,lookup(“PATrackerDatabase2020”,«RecordNumber»,«RecordNumber»,«P1Skills»),lookup(“PATrackerDatabase2020”,«RecordNumber»,«RecordNumber»,«P2Skills»))
This is not formatting very well.
Anyway, this seems to be what Cooper said, so what I would suggest is to look at each element of the ?( using the Formula Workshop to isolate what the problem is. Plug each lookup into it, and see what the result or error is.
You might want to add a default value to your lookups in case the parent has no skills listed. My confusion arose from interpreting your reference to the “search database”. I thought you meant the database being searched, i.e. PATrackerDatabase2020, but now I think you mean the database that is conducting the lookup. The field Order is located in the database conducting the lookup, right?
I note also that this approach assumes that Order in every record is either P1 or P2 and never empty or something else. If that is not the case, then this approach would need to be revised to account for that possibility.
Thanks to Jim for showing us how to format code for the forum, and in a procedure, to make it more legible.
No bother. I don’t think you have the correct formula in your most recent post. See my last post. The first part of the ?( function should be Order=“P1”.
I do not see a missing parenthesis in the formula that I posted. (The last ends with two parentheses, one completing the lookup( function and one completing the ?( function.) Also “P1” must be quoted, since it is a text value that you looking for in the Order field.
Another tool, in case you haven’t found it yet, is the Formula Workshop. You can post a formula there to check if it is correct, and make changes, etc. before you employ it. (I think you may have to omit the carriage returns used for clarity in the forum.) There you can practice with the ?( function and lookup( function, or any other functions.
Thanks @CooperT - when I enter that as in a procedure I still get “Missing right parenthesis”. I tried in the Formula Workshop and it gives me the error “Expression contains a function when an operator was expected.”
Appreciate all the advice – this is easily the most complex part of this database. It’s the last big thing I need to knock out, so thanks again everyone for all the help, and have a great Monday!
Try commenting out this line and see if the error goes away. (Put // at the beginning the formula fill statement and that tells the procedure to ignore that line.) I could be wrong, and sometimes it’s hard to see our own mistakes, but I don’t see a missing parenthesis in the statement. Maybe it’s in another statement. I hope someone smarter than I can see the problem.
The last fly in the ointment is that for some reason, this operation knocks out the Record Number in the Search Database. Previously, each parent had a record number matching one record in the tracking database (where one record contains both parents.) But after this procedure, the Record Number in the search database goes blank or goes to 0. Here are some visuals:
This didn’t happen when I wasn’t using an if statement, like this:
formulafill lookup(“PATrackerDatabase2020”,«RecordNumber»,«RecordNumber»,«P1Skills») so I’m wondering if somehow the if statement is affecting the record number in a way I don’t understand.
BTW, the way it’s numbered is so each individual parent has the same record number as the matching record in the tracking database. The record has both parents in one record. So two consecutive P1s and P2s in the search database will have the same record number, like this:
1
1
2
2…
Skills ARE updating correctly now (thank you!) I appreciate all the help - and see the light at the end of the tunnel
Just a quick update. As a workaround, I wrote a loop procedure to replace the blanks and zeroes with with the correct “doubling” sequence. Since I never re-sorted the records, it works, but it feels a little fragile and takes awhile, so I don’t think it’s a long-term solution.
If anyone has an idea why using the if statement wiped out my record number I’d really appreciate it (database is almost done!) Appreciate all the previous replies that helped get me here.
Thanks @CooperT - there’s not really any other code in there, so I’m thinking I must’ve done something weird/wrong/silly along the way. Tonight I’m going to try to rebuild the search database from scratch, and see if anything changes. Thank you for all the support.
Right now it only prints P1Skills to every record. Thank you for any thoughts on how to fix my mistake and implement your suggestion correctly.
