Hi Manuel, 

You do not need a custom field. You should have a standard field (OOTB) called Default Project. This is a reference field to Project object. In the import file, you will need to specify this column with the Project ID you want to reference the Expense Sheet to. This should work for you. You can download the Expense Sheet entity fields file from Data Loader for better guidance on how to format the upload file.

Thanks @bkrutiy but only if it was that simple!  Our company doesn’t use the same Project ID as the one set by default in AdaptiveWork so I had to create a new field before at the Project level and now the Expense sheet level. And our finance department who will be doing the import process via Dataloader only has that “other” Project ID to reconciliate the expenses against the correct project. 

So my question is how from a field in dataloader called “custom project ID” for example, do I find the corresponding Project in AW?

It’s almost like I would need a “where” statement if it was SQL. I had a look at the different functions but unfortunately can’t use the function FindObject in a WFR. I would need something similar I guess?

Hi Manuel, 

Thank you for clarifying. One way that may work is to have a WFR run when the expense sheet is created or edited and isTriggeredBy(API). When you upload the data, the WFR should have a variable that will pull the Project based on the filter…

TargetObject.C_ProjectID = $C_ProjectID… where the first reference is the custom Project ID on the Project and the second is the custom ID on the expense sheet. If they match, then use that project as the reference. 

Hope this meets your requirements.

Hi Boris

That’s exactly what I was trying to achieve but couldn’t find a way to filter or create that variable unfortunately. Would you be able to send me some screenshots in case I’m missing something basic?

Thanks!

Manuel

I do not have that use case available and would need to build it from scratch. The issue, I am guessing is that you are unable to run on Projects from the expense sheet because you do not have that reference. You need to have a reference point (lookup) that can gain access to all Projects to do a “run on” and filter on the custom Project ID match. That will give you what you are looking for. This does take extra steps to work. 

For example, use an object where you can run a WFR/SWFR that will add any Projects that contain a custom Project ID. I have used Topics as an example. You can add these Projects to a topic you create and when all the Projects are updated there, you can use the Topic.Related Projects as a reference point to do the RUN ON and pull the Project with the matching custom Project id. Just one possible way of doing it. I am sure there are other ways.